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

MS SQL Increase the primary key by 15000

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 ?
0
meperera
Asked:
meperera
1 Solution
 
aesmikeCommented:
dbcc checkident (<your table>, RESEED, <new seed>)
0
 
mepereraAuthor Commented:
thanks aesmike,
  could you please explain this process ?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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

SET IDENTITY_INSERT parenttable OFF
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
aesmikeCommented:
meperera,
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.
0
 
YveauCommented:
Meperera,

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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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