SSIS returns the same identity each time table is truncated and Inserted with the same identity values

  • Hi,
    I have a situation like I have to insert the records from one source to destination table which is having identity filed in it.
    I have to confirm that if I insert the record in the destination table.
    Now if I have to reload the table after truncation using the same source and rerun the SSIS package which will first Truncate the table and then reload the destination table in the same environment then will each row get the same identity value that was assigned when it was first loaded.

    I have a doubt and not able to confirm. There is no change in the source and destination just we re-run the SSIS package which Truncate and reinsert the records.

    Please help on the same.

    Regards
    Amit

  • Well if all you're ever  doing with the target table is truncating it and copying the source table into it just turn the identity off in the target table.

  • ZZartin - Tuesday, January 9, 2018 10:42 AM

    Well if all you're ever  doing with the target table is truncating it and copying the source table into it just turn the identity off in the target table.

    Well Thanks. Actually, I don't want the identity to be inserted externally. I want identity value to be created itself for the destination table.
    I think we just need to confirm that the order in which data coming from source, that must be same then anytime data is populated it will receive the same identity value in multiple run.
    So, if there is a primary key on a source table then it must pick the the data in the same order even if I dont use the order by clause. Not sure really.

  • ammit.it2006 - Tuesday, January 9, 2018 10:51 AM

    ZZartin - Tuesday, January 9, 2018 10:42 AM

    Well if all you're ever  doing with the target table is truncating it and copying the source table into it just turn the identity off in the target table.

    Well Thanks. Actually, I don't want the identity to be inserted externally. I want identity value to be created itself for the destination table.
    I think we just need to confirm that the order in which data coming from source, that must be same then anytime data is populated it will receive the same identity value in multiple run.
    So, if there is a primary key on a source table then it must pick the the data in the same order even if I dont use the order by clause. Not sure really.

    Why are you trying to recreate the order of the identity instead of just loading the column in the target table?  There are a number of ways that could get out of sync, for example if you end up with a gap in the identity in the source table.

  • ammit.it2006 - Tuesday, January 9, 2018 10:51 AM

    Well Thanks. Actually, I don't want the identity to be inserted externally. I want identity value to be created itself for the destination table.
    I think we just need to confirm that the order in which data coming from source, that must be same then anytime data is populated it will receive the same identity value in multiple run.
    So, if there is a primary key on a source table then it must pick the the data in the same order even if I dont use the order by clause. Not sure really.

    When you truncate a table, the identity value is reset to the original seed value. You can find that in the documentation for truncate table:
    Truncate Table

    However in SQL  there is never any guarantee on order without an order by clause.

    Sue

  • Sue_H - Tuesday, January 9, 2018 11:40 AM

    ammit.it2006 - Tuesday, January 9, 2018 10:51 AM

    Well Thanks. Actually, I don't want the identity to be inserted externally. I want identity value to be created itself for the destination table.
    I think we just need to confirm that the order in which data coming from source, that must be same then anytime data is populated it will receive the same identity value in multiple run.
    So, if there is a primary key on a source table then it must pick the the data in the same order even if I dont use the order by clause. Not sure really.

    When you truncate a table, the identity value is reset to the original seed value. You can find that in the documentation for truncate table:
    Truncate Table

    However in SQL  there is never any guarantee on order without an order by clause.

    Sue

    Thanks for all the above suggestions. I think in this case it will be good to create row_number() and set the identity off before inserting. That way it will keep the identity values same until the source has no modifications.

    Regards,
    Amit

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply