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
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER