Copy Table but change Identity seed

Posted on 2008-11-14
Last Modified: 2013-11-30
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

Question by:santa-clara
    LVL 32

    Accepted Solution

    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.

    Author Closing Comment


    Author Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now