none
SqlBulkCopy insert order RRS feed

  • 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.


    • Moved by Mike Danes Thursday, July 2, 2015 5:36 AM ASO.NET specific
    Thursday, July 2, 2015 4:50 AM

Answers

  • Hello Brody03,

    >> So the following would work?

    Yes, this should be ok and the order of records would keep the same(sorry for a delay reply).

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Brody03 Tuesday, July 7, 2015 4:56 AM
    Tuesday, July 7, 2015 1:35 AM
    Moderator
  • Thanks for the reply.

    So the following would work ?

    • 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.

    Thanks!

    • Marked as answer by Brody03 Tuesday, July 7, 2015 4:56 AM
    Friday, July 3, 2015 5:35 AM

All replies

  • Hello Brody03,

    >>Question: Whether id in resultDt will represent id in dt for all the rows ? i.e,.

    As far as I know, there is not guarantee for the sqlbulkcopy would perform a order bulk copy. A test I made with 1000 rows shows the query table would be in a order, however, in the physical drive, we do not sure if they are also in a order. I suggest you use a writeable id so that the order would keep the same even you using the sqlbulkcopy.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 3, 2015 2:34 AM
    Moderator
  • Hello Fred Bao,

    What is a writeable id ? Could you provide one example ?

    You mean to remove the identity column and add an id column without identity ?

    Thanks !


    • Edited by Brody03 Friday, July 3, 2015 4:56 AM
    Friday, July 3, 2015 4:42 AM
  • Hello,

    I mean do not use the identity column for your id column.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 3, 2015 5:13 AM
    Moderator
  • Thanks for the reply.

    So the following would work ?

    • 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.

    Thanks!

    • Marked as answer by Brody03 Tuesday, July 7, 2015 4:56 AM
    Friday, July 3, 2015 5:35 AM
  • Hello Brody03,

    >> So the following would work?

    Yes, this should be ok and the order of records would keep the same(sorry for a delay reply).

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Brody03 Tuesday, July 7, 2015 4:56 AM
    Tuesday, July 7, 2015 1:35 AM
    Moderator