SqlBulkCopy insert order

c# sql sqlbulkcopy

Question

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.

Server1, db1

TableA
id   name
1    name10
2    name20
3    name30
4    name40

Server2, db1

TableA
    id   name
    1    name1
    2    name2
    3    name3
    4    name4
    ..........
    ..........
    5000 name22
    5001 name33
  • Step1: var dt = select * from server1.dbo.TableA order by id;
  • Step2: SQL bulk copy into server2 bulkCopy.WriteToServer(dt);
  • Step3: 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".
  • Step4: 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.

1
3
6/30/2015 1:47:36 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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 :)

1
7/6/2015 4:23:28 AM

Popular Answer

Another solution is available in the below link:

https://social.msdn.microsoft.com/Forums/en-US/ee3d90b0-d212-4bf0-8f2a-5dcb716896eb/sqlbulkcopy-insert-order?forum=adodotnetdataproviders

  • Create staging table(say tmp_tableA) with same structure of tableA without identity.
  • Bulk copy the data into tmp_tableA.
  • Now insert into tableA from tmp_tableA.
Rocky

Related Questions

How does SqlBulkCopy retain row order?

After lots of research it seems evident that there's no way to retain row order with the Bulk Insert command written as it is featured by Microsoft. You either have to add an ID column yourself directly into the import file, use a shell or other external script, or you do without. It seems it would be a needed (and easy) feature for Microsoft to ad...
excel export import sqlbulkcopy sql-server
asked by Dean Bell

When there is no primary key, how can SqlBulkCopy prevent duplicate entries from being inserted?

What is the data volume? You have 2 options that I can see:...1: filter it at source, by implementing your own ...IDataReader... and using some hash over the data, and simply skipping any duplicates so that they never get passed into the TDS....2: filter it in the DB; at the simplest level, I guess you could have multiple stages of import - the raw...
c# sql sqlbulkcopy sql-server sql-server-2008
asked by Marc Gravell

How can I put data into a table in a non-default database schema using SqlBulkCopy?

While the API does not provide an explicit interface to set the destination schema, you can actually stuff a 2- or 3-part qualified table name into the ...DestinationTableName... property like so:...b.DestinationTableName = string.Format("[{0}].[{1}]", schemaName, tableName); ...or...b.DestinationTableName = string.Format("[{0}].[{1}].[{2}]", d...
.net database-schema schema sqlbulkcopy
asked by Jon Seigel

Is the order retained in a SQL bulk insert with parent/child relationships?

This is not an absolut bulk insert, but instead it inserts all child data at the same time with the parent data, making only 1 round trip to the DB....insert into parent(groupcode, parentdata) values(1, 'parent data'); insert into child(parentrecno, childdata) select parentrecno, childdata from ( select SCOPE_IDENTITY() as parentrecno, 'child d...
c# sqlbulkcopy sql-server-2008
asked by Fede

When using SqlBulkCopy to insert into a sql variant column, how do I specify the column type?

According to the MSDN page for ...SqlBulkCopy... (under "Remarks"):...SqlBulkCopy will fail when bulk loading a DataTable column of type SqlDateTime into a SQL Server column whose type is one of the date/time types added in SQL Server 2008....So, ...SqlBulkCopy... won't be able to handle ...DateTime2... values. Instead, I'd suggest one of two ...
ado.net datatable sqlbulkcopy sql-server sql-variant
asked by chezy525

Insert Dictionary elements into SQL table as rows using IDatareader and SqlBulkCopy

As you already have figured out, you need to implement custom ...IDataReader.... As your source is Dictionary - only few relatively simple method/properties such as int FieldCount, bool Read(), object Get(int i) should be implemented....You may find useful examples (...a simple one...) of custom IDataReader implementations by googling ...sqlbulkcop...
c# datareader dictionary sql sqlbulkcopy
asked by kasitan

SqlBulkCopy rows from one table to another but with different columns order

This is what I use for ...SqlBulkCopy... in my applications...SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStringSettings.ConnectionString); bulkCopy.BulkCopyTimeout = 0; // Sets the timeout to unlimited // Iterates through each column in the datatable foreach (DataColumn column in table.Columns) { // Makes a connection map between the d...
.net sqlbulkcopy sql-server
asked by TFischer

SqlBulkCopy hangs without any exception or timeout when inserting a datatable with even a single row

Fixed it. Spelt the table name wrong, was inserting into CuteCats instead of CuteCat. It hung with no error message. Blasted thing!
c# sqlbulkcopy
asked by NibblyPig

SqlBulkCopy inserts when inside a transaction prevents any other writes on a table

The ...IsolationLevel... only refers to reads and not to writes. If one of your client is writing data, the other client should be able to read the data as it was before the start of the transaction, however, it will not be able to write at the same time.
asp.net c# sql sqlbulkcopy sql-server
asked by Alex

SSIS - OleDB connection not updating data, inserted thru SQLBulkCopy with SQL Connection

My bad....Instead of passing data type as long (int in SQL), i was passing it as Varchar....I was looking from last few hours and as soon as i post question here, it strikes me to check the data type....Hope it will help somebody.
oledbconnection sqlbulkcopy sqlconnection ssis
asked by Manish Joisar

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow