Copy Table but change Identity seed

How do I clone a table so that the new table has a different identity seed and with the existing identity values changed programmatically

I have not been able to get "set identity on/off " to help here ... presumably because 2 tables are involved
(set new-id = 1160000 + old-id%1140000)

Open in new window

Who is Participating?
Daniel WilsonConnect With a Mentor Commented:
Not sure where the problem is on the 2-table setup.

Create Table NewTable(NewID int Not NULL Identity(1160000, 3),  field2, field3, etc)goSet identity_insert NewTable ongoinsert into NewTable(NewID, field2, field3, etc)(Select 1160000 + old-id%1140000, field2, field3, etc from OldTable)goSet Identity_Insert NewTable Offgo

Now ... your formula 1160000 + old-id%1140000 might create duplicates depending on the data in OldTable.  That could cause problems.
santa-claraAuthor Commented:
santa-claraAuthor Commented:
Unfortunately I have discovered that the accepted solution does not actually solve my problem.
The issue is the word "clone"
And a further unstated requirement is "Dynamic"
My attempts at a solution involved "select * into TableClone from TableOriginal"
followed by "update TableClone set  ident-id = 1160000+ident-id%1140000"
The big problem I have just discovered is that "select into" does not copy keys and defaults  ie it is not a "clone"
so I really seem to have 2 problems
1) how do I dynamically clone any given table?
2) how can I drop the Identity constraint, update the erstwhile identity col & then reseed it?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.