SQLBULKCOPY function in .NET

Question

Hi I am doing sqlbulkcopy from one table to another table. There are so many tables to do that. I am copying data with for loop for each tables. But i am facing with an issue whenever i copy data from source table to destination table than identity column value is getting changed. Suppoose there is value like this in identity column of a source table 301 305 605 but when i copy data than it will imported in destination table like that 1 2 3 1) I have tried set identity_insert [tablename] ON //COPY Data from source to destination set identity_insert [tablename] OFF

Rajeev Punhani · Answer

Hi Murugavel To preserve the identity values from source to destination table you can use SqlBulkCopyOptions.KeepIdentity in the connection string. While doing a bulk insert you have two options - 1 - use KeepIdentity and preserve the source's Identity values. 2 - Don't map the Identity field. If you don't try to assign a value the target table will assign one automatically. using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString SqlBulkCopyOptions.KeepIdentity)) Please have a look at the below link for reference. https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions(v=vs.110).aspx

Tapan Patel · Answer

Your code of three lines looks good. I mean there is no way it shouldn't work except you are trying to set Indentity on for other table than the destine one. set identity_insert [tablename] ON //COPY Data from source to destination set identity_insert [tablename] OFF