Bulk Copy with IDENTITY

Joshua Harris

15y8.9kVisual Basic .NET

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_ID","FSB_ID","TMR","Qty","EVENT_DATE","job_number"
2473887,"BOS","FJ780",1,2011-05-19 12:48:12,"G3191116"
2473888,"BOS","G377T",1,2011-05-19 12:48:14,"G3191131"
2473889,"BOS","HP592",1,2011-05-19 12:48:14,"G3191131"
2473926,"DC","28Y5M",1,2011-05-19 12:54:12,"G3191315"

I need to upload this into a SQL table and keepidentity. Also need to start the import at the 2nd line (ignoring the header)

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Joshua Harris15y ago
thanks!!!!
0
Pradeep Chandraker15y ago
Not on the database table. Try to set identity column at the last in ColumnMappings on your C# code.
0
Joshua Harris15y ago
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?
0
Pradeep Chandraker15y ago
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.
0
Joshua Harris15y ago
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
        {
         SqlBulkCopycopy = 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);
         }
}
0
15y ago
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.
0