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.

Solution 1

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.

Solution 2

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

Solution 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.

Solution 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.

Solution 5

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.

View more solutions

Comments

[Admin]

This is a custom id, not auto incremented / generated by the database.

[Admin]

Updating DB schema not an option here, need a function or sql query to do this as it's a custom id.

[Admin]

This is not an auto-id field.

[Admin]

Updating DB schema not an option here, need a function or sql query to do this as it's a custom id.

[sybkar]

Careful with this one...they haven't specified that they're using Sybase SQLAnywhere, they've only said 'Sybase', so there's a pretty good chance that they're referring to Sybase ASE, which does not have the ROW_NUMBER function to my knowledge?

[Fabio]

@sybkar, thanks for you comment. In this case I recommend the approach pointed by Jarek Bielicki, or using a temporary table with identity column as a intermediary table if user1464435 wants to guarantee its sequential new ids.

[Tim Lehner]

+1. I would suggest this as well as long as it's not done frequently on a large table (performance issue w/ max) and it's okay to change the similiarID "key" (no referential problems). Otherwise, they might need to roll their own sequence or use a temp table with an identity.