Insert multiple rowsvia query...duplicate id error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert multiple rowsvia query...duplicate id error

Author

Topic

sth_Weird
Starting Member

38 Posts

Posted - 2012-06-04 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-04 : 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