| Author |
 Topic  |
|
|
sth_Weird
Starting Member
Germany
31 Posts |
 Posted - 06/04/2012 : 08:04:07
|
hi,
I am trying to insert data into a table. My query is something like this:
INSERT INTO MyTable (Col1, Col2, Col3) (SELECT (ColA, ColB, ColC) FROM MyOtherTable WHERE...)
Besides the columns Col1-Col3, MyTable also has an "ID" column (Primary Key). The ID is not an autogenerated value. The default value of the ID column points to a function that is supposed to generate a new ID for newly inserted rows. This function queries the maximum ID value from MyTable and returns value + 1. This works very well when inserting only one row at a time.
When I run the INSERT... query given above, I get an error saying that you cannot insert duplicate values into the ID column. I suppose that it tries to insert all the rows returned by the subquery in one transaction and only determines the default value = ID once (so it tries to insert the same ID into all new rows)?
I don't know how to solve this. I cannot change the ID column of the table because it is not mine. Is there a way to tell a query to insert the new rows one after the other, so that the "determine a new ID"-function is called for each row? Any workarounds?
Any help is appreciated!
thank you!!!
sth_Weird |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 06/04/2012 : 08:43:19
|
declare @offset int
select @offset=max(id) from DestTable
insert DestTable(Id, Col1, Col2, Col3)
select row_number() over (order by Id) + @offset, ColA,ColB,ColC from SourceTable where ...
No, you're never too old to Yak'n'Roll if you're too young to die.
edit: typo |
Edited by - webfred on 06/04/2012 08:44:10 |
 |
|
|
sth_Weird
Starting Member
Germany
31 Posts |
Posted - 06/06/2012 : 05:45:03
|
works great :)
thanx a lot!
sth_Weird |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 06/06/2012 : 06:56:48
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
| |
Topic |
|
|
|
Insert multiple rowsvia query...duplicate id error
