SQLBULKCOPY function in .NET

Murugavel S

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

2 Replies

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

Rajeev Punhani9y ago
Accepted 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
+1
Tapan Patel9y ago
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
0