SQL SELECT INSERT INTO Generate Unique Id

问题:

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.

回答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

回答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.

回答3:

Im not sure if I understad you correctly: You want to copy all data from TABLE2 but be sure that TABLE2.similiarId is not alredy in TABLE1.id, maybe this is solution for your problem:

DECLARE @idmax INT
SELECT @idmax = MAX(id) FROM TABLE1

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

Now insert will not fail because of primary key violation because every inserted id will be greater then id witch was alredy there.

回答4:

If the id field is defined as auto-id and you leave it out of the insert statement, then sql will generate unique id's from the available pool.

回答5:

In SQL Server we have the function ROW_NUMBER, and if I have understood you correctly the following code will do what you need:

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

ROW_NUMBER will bring the number of each row, and you can add a "magic value" to it to make those values different from the current max ID of TABLE1. Let's say your current max ID is 6, then adding 6 to each result of ROW_NUMBER will give you 7, 8, 9, and so on. This way you won't have the same values for the TABLE1's primary key.

I have asked Google and it said to me that Sybase has the function ROW_NUMBER too (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbusage/ug-olap-s-51258147.html), so I think you can try it.

回答6:

If you want to make an identical table why not simply use (quick and dirty) Select INTO method ?

SELECT * INTO TABLE2
FROM TABLE1 

Hope This helps.

回答7:

Make the table1 ID IDENTITY if it is not a custom id.

or

Create new primary key in table1 and make it IDENTITY, and you can keep the previous IDs in the same format (but not primary key).

回答8:

Your best bet may be to add an additional column on Table2 for Table1.Id. This way you keep both sets of Keys.

(If you are busy with a data merge, retaining Table1.Id may be important for any foreign keys which may still reference Table1.Id - you will then need to 'fix up' foreign keys in tables referencing Table1.Id, which now need to reference the applicable key in table 2).

回答9:

If you need your 2nd table keep similar values as in 1st table , then donot apply auto increment on 2nd table.

回答10:

If you have large range, and want easy fast make and don't care about ID:

Example wit CONCAT

INSERT INTO session(SELECT CONCAT("3000", id) as id, cookieid FROM `session2`)

but you can using also REPLACE