|
sogorman
Starting Member
5 Posts |
 Posted - 2010-12-21 : 08:34:23
|
| I have a table with about 13 columns. 3 of them are key columns with auto incrementing values. I am trying to import data from one table into that table but only want to specify data for 12 of the columns (2 of which are key columns) and have SQL just auto increment the one remaining key column. The only way I have found to be able to insert data in the table and specify data for some of the key columns is using Insert Identity = on but when doing that I have to supply data for all the columns. Here is my syntax which works if supplying data for all 3 of the key columns (OffsetTrack, Offset, AutoInc) in the PlayTableOffset table. I would like to leave the AutoInc column alone and just have SQL bump that up by one for every new row.SeanCREATE TABLE PlayTableOffsetHolding ("OffsetTrack" char(15), "Offset" char(12), "AutoInc" int, "Action" char(255), "Device" char(255), "Option1" char(255), "Option2" char(255), "Option3" char(255), "Option4" char(255), "Option5" char(255), "Option6" char(255), "Option7" char(255), "Option8" char(255), "Option9" char(255), "Option10" char(255) )INSERT INTO PlayTableOffsetHolding (OffsetTrack) SELECT PlayTableHolding.OffsetTrack FROM PlayTableHoldingUpdate PlayTableOffsetHolding SET Offset='+00:00:20:00', "AutoInc" = '12345', "Action" = 'RunMacro', "Device" = 'Macro', "Option1" = 'Cox7InHD'SET IDENTITY_INSERT PlayTableOffset ONINSERT INTO PlayTableOffset ("OffsetTrack", "Offset", "AutoInc", "Action", "Device", "Option1", "Option2", "Option3", "Option4", "Option5", "Option6", "Option7", "Option8", "Option9", "Option10") SELECT "OffsetTrack", "Offset", "AutoInc", "Action", "Device", "Option1", "Option2", "Option3", "Option4", "Option5", "Option6", "Option7", "Option8", "Option9", "Option10" FROM PlayTableOffsetHolding |
|
Using IDENTITY_INSERT with 3 key columns