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?
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