FreeTDS Development Group

["Thompson, Bill D (London)" <ThompBil AT exchange.uk.ml.com>]

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