John Smith John Smith - 5 months ago 17
SQL Question

sql conversion script

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

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
Comments