SQL SELECT INSERT INTO Create a Unique Identifier

Question

I am trying to select a data table and paste this data into another file with similar column names (this essentially duplicates the data). Current syntax:

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

I have a problem with creating unique key fields (declared as integers) for newly inserted records. I can not use table2, because there is existing data in table 1 and there will be an error when duplicating key values.

I cannot change the table schema, and these are user identifier columns that are not automatically generated by the database.

Answer 1

Does table 1 have an auto-increment value in the id field? If so, can you lose the similiarId from the insert and let the auto-increment take care of the unique keys?

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

Answer 2

Not sure if I understand you correctly: You want to copy all the data from TABLE2, but make sure that TABLE2.similiarId is not alredy in TABLE1.id, maybe this is the 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 the insertion will not fail due to a violation of the primary key, because each inserted identifier will be larger than the id that was alredy there.

Answer 3

At your request, you need to make a request as follows:

 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 i did here:
Added ROW_NUMBER() , which starts with 1, so MAX() added for the identifier of the ROW_NUMBER() table.

For a better explanation, see this SQLFiddle.

Answer 4

If the id field is defined as auto-id and you leave it outside the insert statement, then sql will generate a unique identifier from the available pool.

Answer 5

In SQL Server, we have the ROW_NUMBER function, and if I understand 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 result in the number of each row, and you can add a “magic value” to it so that these values ​​differ from the current maximum table ID TABLE1. Say your current maximum ID is 6, and then adding 6 to each ROW_NUMBER result will give you 7, 8, 9, etc. Thus, you will not have the same values ​​for the primary key TABLE1.

I asked Google and he told me that Sybase also has a ROW_NUMBER function ( 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.

Answer 6

If you want to create an identical table, why not just use (quick and dirty) Choose the INTO method?

 SELECT * INTO TABLE2 FROM TABLE1 

Hope this helps.

Answer 7

Make the table ID1 if it is not a user id.

or

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

Answer 8

It is best to add an extra column to Table2 for Table1.Id. This way you save both sets of keys.

(If you are busy combining data, saving Table1.Id can be important for any foreign keys that can still reference Table1.Id - you will need to “fix” the foreign keys in the tables that reference Table1.Id, which should now refer to the corresponding key in table 2).

Answer 9

If you need your second table, keep the same values ​​as in the 1st table, then do not use auto increment on the 2nd table.