sql conversion script

Question

I have a 2 databases that I want to merge with some similiar tables. The source tables have id as bigint and my destination table has int as ID. There aren't that many records in my source table (< 20k) so I want to assign new ids to all records so the ids can fit in an int. How can I do this with sql?

Answer 1

First Option

You can Use Sequence object as follow:

First Create a Sequence object and assign it's Start With value to max Id value in destination table plus 1. For example if max Id in destination table is 100, you need to assign 101 as Start With. You can also obtain the max Id value from destination table using a Max(Id) aggregate function and store it in a variable:

CREATE SEQUENCE SeqId
START WITH [Max value of Id in destination table]  
INCREMENT BY 1 ;  
GO  

Then insert to destination table using following query:

Insert Into tblXXX (Id, ...) Values (NEXT VALUE FOR SeqId, ...)

Read more about Sequence Object

Second Option

You can make the destination table's Id column as Identity column with seed equal to destination table's Id column max value and Increment equal to 1. Here is detailed example also Here

Answer 2

You did not provide much details so I can only provide a general guideline:
Note: Example assumes that you want to merge tables A and B into C and you want to generate new IDs. I also assume that these IDs are not referenced by other tables (foreign keys).

First you get record counts from tables A and B:

DECLARE @countA INT
DECLARE @countB INT
SET @countA = ( SELECT COUNT(*) FROM A )
SET @countB = ( SELECT COUNT(*) FROM B )

Next you use a window function to generate new IDs and insert records into table C.

INSERT INTO C
SELECT @countA + ROW_NUMBER() OVER( ORDER BY ID ) AS ID, ....
FROM A

INSERT INTO C
SELECT @countA + @countB + ROW_NUMBER() OVER( ORDER BY ID ) AS ID, ....
FROM B