[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 870
  • Last Modified:

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

  • 2
1 Solution
Daniel WilsonCommented:
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?

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now