MS SQL Increase the primary key by 15000

Posted on 2007-08-11
Last Modified: 2013-11-05
Hi experts,
  I am combining two MS SQL databases. The primary key of the of the main table of the databases is CustNumber. Since both tables have numbers starting from 1. I am thinking about incrementing the number of the second table by 15000. (Example, First database will have numbers starting from 1 and the second from 15001). How can I achieve this ? Is there an SQL command that can do this ?
Question by:meperera
    LVL 10

    Expert Comment

    dbcc checkident (<your table>, RESEED, <new seed>)

    Author Comment

    thanks aesmike,
      could you please explain this process ?
    LVL 24

    Expert Comment

    by:DBAduck - Ben Miller
    Well, you would want to insert into the table that is becoming the main one with the numbers incremented.

    SET IDENTITY_INSERT parenttable ON

    INSERT INTO parenttable (CustNumber, otherfield, otherfield2)
    SELECT CustNumber+15000, otherfield, otherfield2
    FROM secondarytable

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    it is not 100% clear what you are trying to do:

    * update the existing records in the second table?
     -> you need to insert the records into the same table with the IDENTITY_INSERT as shown, and then delete the original records.
     NOTE: if you have foreign keys pointing to that table, you will need to update those values in between the insert and the delete!

    * select from both tables, but make the PK values from the second tables only +15000
     -> just use a UNION query, with the query from the second table with PK+15000

    * copy over the rows from the second table to the first table
     -> dbaduck's code is what you need.
    LVL 10

    Accepted Solution

    from Query Analyzer, open a query window.
    type this into the window:
       dbcc checkident (custtable, RESEED, 15001)
    Execute the query.

    The seed number is the next number used in the identity sequence.  The command I gave you allows you to change the seed number to whatever you like.  What you want to do is common with database replication.  Databases in different locations need to use different Identity sequences so they don't step all over each other and you have duplicates.
    LVL 18

    Expert Comment


    Everyone is taking for granted that the column you mentioned is an identity column. Check that as well, if not all answers given do not apply. Check first !
    I have to admit that it sure looks like it, but you'd better be safe than sorry.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In this article I will describe the Backup & Restore 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.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    732 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

    17 Experts available now in Live!

    Get 1:1 Help Now