Bulk Copy with IDENTITY

Question

CREATE TABLE [dbo] . [litSDO] ( [litSDOId] [int] IDENTITY ( 1  1 ) NOT NULL [OrderId] [int] NULL [FSBId] [nvarchar] ( 50 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [TMR] [nvarchar] ( 50 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [Qty] [int] NOT NULL [EventDate] [smalldatetime] NULL [JobNumber] [varchar] ( 50 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] File : (.txt) Order_IDFSB_IDTMRQtyEVENT_DATEjob_number 2473887BOSFJ78012011-05-19 12:48:12G3191116 2473888BOSG377T12011-05-19 12:48:14G3191131 2473889BOSHP59212011-05-19 12:48:14G3191131 2473926DC28Y5M12011-05-19 12:54:12G3191315 I need to upload this into a SQL table and keepidentity. Also need to start the import at the 2nd line (ignoring the header)

Joshua Harris · Answer

thanks!!!!

Pradeep Chandraker · Answer

Not on the database table. Try to set identity column at the last in ColumnMappings on your C# code.

Joshua Harris · Answer

Unfortunately this is still on SQL 2000 (business owned CSS) not IT supported and they will not take it the way it is. So 2000 is what I have to work with. Just to clarify are you saying set the identity in the table as the last column?

Pradeep Chandraker · Answer

Instead of mapping identity column first try to map it at the last. Also if you are using SQL Server 2008 you can pass structure type to stored proc which is really an interesting enhancement.

Joshua Harris · Answer

Thanks for the reply but tring to avoid DTS SSIS etc.. as this class will be used for multipule file formats. Using C#... looking into SQLBulkCopy but cant get the Keepidentity to work. I have to be missing something easy.. ........build data table rows using ( SqlConnection cn = new SqlConnection ( cs .AppSettings[ ConnString ].ToString())) { cn.Open(); try { SqlBulkCopy copy = new SqlBulkCopy (cn); { copy.ColumnMappings.Add(0 0); copy.ColumnMappings.Add(1 1); copy.ColumnMappings.Add(2 2); .....etc.... copy.DestinationTableName = SqlBulkCopy copy = new SqlBulkCopy cn); myTable copy.WriteToServer(dt); } }

Muralidharan Deenathayalan · Answer

You can use SQL server Data Import /Export wizard. Keep all the values into excel. By using the data import /export wizard select the source as excel  here you can mention the first row is the header and you can ignore it. After the mapping the destination database and table by using the mapping option you can enable identity insert as well. Hope this helps you.