Link to home
Start Free TrialLog in
Avatar of santa-clara
santa-clara

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of santa-clara
santa-clara

ASKER

thanx
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?