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

SQLBULKCOPY function in .NET

Answers (2)