meperera
asked on
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 ?
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 ?
dbcc checkident (<your table>, RESEED, <new seed>)
ASKER
thanks aesmike,
could you please explain this process ?
could you please explain this process ?
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
SET IDENTITY_INSERT parenttable ON
INSERT INTO parenttable (CustNumber, otherfield, otherfield2)
SELECT CustNumber+15000, otherfield, otherfield2
FROM secondarytable
SET IDENTITY_INSERT parenttable OFF
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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.
* 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.