SQL SELECT INSERT INTO Generate Unique Id

Question

I'm attempting to select a table of data and insert this data into another file with similar column names (it's essentially duplicate data). Current syntax as follows:

INSERT INTO TABLE1 (id, id2, col1, col2)
SELECT similiarId, similiarId2, similiarCol1, similiarCol2  
FROM TABLE2

The problem I have is generating unique key fields (declared as integers) for the newly inserted records. I can't use table2's key's as table1 has existing data and will error on duplicate key values.

I cannot change the table schema and these are custom id columns not generated automatically by the DB.

Answer 1

Does table1 have an auto-increment on its id field? If so, can you lose similiarId from the insert and let the auto-increment take care of unique keys?

INSERT INTO TABLE1 (id2, col1, col2) SELECT similiarId2, similiarCol1, similiarCol2
FROM TABLE2

Answer 2

As per you requirement you need to do you query like this:

INSERT INTO TABLE1 (id, id2, col1, col2)
SELECT (ROW_NUMBER( ) OVER ( ORDER BY ID ASC )) 
+ (SELECT MAX(id) FROM TABLE1) AS similiarId
, similiarId2, similiarCol1, similiarCol2  
FROM TABLE2

What have I done here:
Added ROW_NUMBER() which will start from 1 so also added MAX() function for ID of destination table.

For better explanation See this SQLFiddle.