SQL SELECT INSERT INTO Generate Unique Id

sql database select insert sybase
jrb picture jrb · Jun 18, 2012 · Viewed 43.5k times · Source

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

alex stacey picture alex stacey · Jun 18, 2012

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

Related questions

How to select bottom most rows?

I can do SELECT TOP (200) ... but why not BOTTOM (200)? Well not to get into philosophy what I mean is, how can I do the equivalent of TOP (200) but in reverse (from the bottom, like you'd expect BOTTOM to do...)?

Read More
Selecting rows where remainder (modulo) is 1 after division by 2?

There is a column in options that hold an integer. I want to select the row only if that value % 2 = 1. I know this can be done in 2 queries but is it possible to do it in 1?

Read More
select max, min values from two tables

I have two tables. Differ in that an archive is a table and the other holds the current record. These are the tables recording sales in the company. In both we have among other fields: id, name, price of sale. …

Read More