Click here to Skip to main content
14,659,894 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,
I am using SqlBulkCopy to insert my DataTable data into a sql table. I am sorting the data in my c# Datatable in desired manner and inserting them. But when using BulkCopy it is not inserting in sequential manner. Please advice me how to insert the records in sequential manner using SqlBulkCopy


SqlBulkCopy bcp = new SqlBulkCopy(SqlConnectionString);
              bcp.ColumnMappings.Add("LineUp", "LineupID");
              bcp.ColumnMappings.Add("ChannelName", "ChannelName");
              bcp.ColumnMappings.Add("ChannelNumber", "ChannelID");
              bcp.ColumnMappings.Add("ItemID", "ItemID");
              bcp.ColumnMappings.Add("CLUIId", "CLUIId");
              bcp.DestinationTableName = "_ChannelLineUp";
              bcp.WriteToServer(dv.Table);



In the above code I do not have any identity column which is mapped to the table. please review code and suggest me the required changes.

Thanks
Jagan Atmakuri
Posted
Updated 12-Nov-14 3:35am
v2
Comments
PIEBALDconsult 12-Nov-14 9:45am
   
The order of records in a database is not important. Not worth the effort.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You can not do sorting on bulk copy command, What you can do is that copy the data into a temp table and sort that and then insert.

One question is that why you want to sort your data while inserting into a table it adds an extra execution cost , you can sort the data while reading it from the table.

Or other way is to create clustered index on table for the key columns you want to be sorted.
   
Comments
jing567 12-Nov-14 9:51am
   
My problem is that to insert records in a desired sequential order and that sequence is customized via c# code... So i want to insert that sequence into sql table... So any idea about that how to insert in same order?
Maciej Los 12-Nov-14 11:05am
   
What kind of sequence? Maybe it's possible to achieve that via SQL...
Shweta N Mishra 12-Nov-14 9:59am
   
Can you sent the customized sequence to the stored procedure as a parameter and use that inside your SP after bulk copy.
jing567 13-Nov-14 4:15am
   
Hi Shwetha I placed a Sort column in the Database table and tried to order by on that column which solved my problem. Thanks for the solution.
Shweta N Mishra 12-Nov-14 10:07am
   
Refer
https://www.simple-talk.com/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/
http://technet.microsoft.com/en-us/library/ms190421(v=sql.105).aspx

They all need to have the Clustered Index created on your table in same order of sorting.

So the only way for you to have the order by column sent as parameter to the SQL and then sort it after bulk insert.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100