January 9, 2018 at 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.
January 9, 2018 at 10:51 am
ZZartin - Tuesday, January 9, 2018 10:42 AMWell 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.
January 9, 2018 at 11:07 am
ammit.it2006 - Tuesday, January 9, 2018 10:51 AMZZartin - Tuesday, January 9, 2018 10:42 AMWell 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.
January 9, 2018 at 11:40 am
ammit.it2006 - Tuesday, January 9, 2018 10:51 AMWell 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
January 11, 2018 at 12:37 am
Sue_H - Tuesday, January 9, 2018 11:40 AMammit.it2006 - Tuesday, January 9, 2018 10:51 AMWell 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 TableHowever 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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply