To copy data from one database to another in different server with same schema, I am planning to use SqlBulkCopy class from C sharp library. Whether SqlBulkCopy will maintain the same order as it is in the datatable while inserting the records ?
Example: id is the identity column.
TableA id name 1 name10 2 name20 3 name30 4 name40
TableA id name 1 name1 2 name2 3 name3 4 name4 .......... .......... 5000 name22 5001 name33
var dt = select * from server1.dbo.TableA order by id;
var resultDt = select top 4 id from server2.dbo.TableA order by id desc.Since we know the number of records we inserted I am using "top 4".
resultDt.DefaultView.Sort = "id asc";
Question: Whether id in resultDt will represent id in dt for all the rows ? i.e,.
5002 from server2 = 1 from server1 5003 from server2 = 2 from server1 5004 from server2 = 3 from server1 5005 from server2 = 4 from server1
Note: Just for example purpose I have given less records. Actual table contains some thousands of records.
It looks like there is no guarantee of order while bulk insert. So I added a temporary id column to the destination table. Flow will be as follows:
Step1: var dt = select *, id as tempId from server1.dbo.TableA order by id; Step2: SQL bulk copy into server2 bulkCopy.WriteToServer(dt); Step3: var resultDt = select top 4 id, tempId from server2.dbo.TableA order by id desc. Since we know the number of records we inserted I am using "top 4".
Now id will be the new id generated by server2 and tempId will be the id from server1. Problem solved :)
Another solution is available in the below link: