FreeTDS Development Group

["Anwar, Mohammed" <mohammed.anwar AT intel.com>]

James,
        It would help me a lot if we could get this in.
        The workaround (creating a temp table and then doing a select into 
the final table)     is going to slow down the data insertion.       
        
Thanks
Anwar

-----Original Message-----
From: Lowden, James K [mailto:LowdenJK AT bernstein.com]
Sent: Wednesday, April 23, 2003 7:23 AM
To: 'FreeTDS Development Group'
Subject: RE: [freetds] IDENTITY column problem with bcp


Bill, 

I'm working on bcp.c as part of the UTF-8 effort, including how columns are
described.  If you or anyone else can identify how the protocol signals the
server whether to honor or generate values for an Identity column, I could
probably add the logic.  

Current cvs will extract nchar columns and write UTF-8 data files if "client
charset" is UTF-8 in freetds.conf.  Uploading fails to convert [calls
tds_put_n()]; it simply sends the data verbatim.  As far as I can tell,
freebcp never could manage a round trip of nchar data: it would convert the
data to ASCII on extraction, but not "unconvert" them back to UCS-2 on
insertion.  

--jkl

> -----Original Message-----
> From: Thompson, Bill D (London) [mailto:ThompBil AT exchange.uk.ml.com]
> Sent: April 23, 2003 5:33 AM
> To: 'FreeTDS Development Group'
> Subject: RE: [freetds] IDENTITY column problem with bcp
> 
> 
> Anwar,
> 
> Alas, I don't *think* that freebcp will support what you are 
> trying to do
> currently.
> Having had a look around the Sybase and Microsoft 
> documentation for their
> bcp implementations, it looks like we would have to enhance 
> freebcp to cater
> for bulk insert into tables with identity columns.
> 
> For the time being, all I can suggest is that you perform 
> your action in two
> stages:
> 
> 1) insert the data into a version of the table that does not have the
> identity column
> 2) copy the data over to the table that has the identity 
> column using a
> standard INSERT...SELECT statement, and let the server 
> allocate the identity
> column values for you.
> 
> Bill
> 
> > -----Original Message-----
> > From:       Anwar, Mohammed [SMTP:mohammed.anwar AT intel.com]
> > Sent:       22 April 2003 18:44
> > To: freetds AT lists.ibiblio.org
> > Subject:    [freetds] IDENTITY column problem with bcp
> > 
> > Hi,
> >     I am trying to bulk insert values into a table with an Identity
> > column.
> >     
> >     If I do not include the column in my data I receive an error
> > indicating that I am trying to insert a NULL value into a 
> column that does
> > not accept null.
> > 
> >     If I insert a value then that value is actually 
> inserted into the
> > column (rather than created).
> >     I read in some post on the web to use 0 and that the server will
> > automatically insert the identity value.
> >     This does not work either.
> > 
> >     I have tried bcp both from a file and using the bind variables.
> >     
> >     I am using SQL Server 2000, the scripts are running on linux.
> >     The freetds.conf file indicates tds version = 7.0
> > 
> >     The table creation SQL is as follows: 
> >     CREATE TABLE [dbo].[TableName] (
> >             [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> >             [signature] [bigint] NOT NULL ,
> >             [name] [varchar] (128) COLLATE Latin1_General_BIN NULL
> >     ) ON [PRIMARY]
> > 
> >     I would like to insert the data from variables or a file without
> > specifying the value for the id column.
> >     Is there any way to specify to bcp that the id column is an
> > identity.
> > 
> >     The id field is also the primary key which I create 
> after the data
> > has been inserted. So I need for it to be NOT NULL.
> >     
> >     I would really appreciate some help on this.
> > 
> > Thanks
> > Anwar
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS AT lists.ibiblio.org
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 


The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above.  If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the
original message. Please note that we do not accept account orders and/or
instructions by e-mail, and therefore will not be responsible for carrying
out such orders and/or instructions.