SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Insert multiple rowsvia query...duplicate id error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sth_Weird
Starting Member

Germany
36 Posts

Posted - 06/04/2012 :  08:04:07  Show Profile  Reply with Quote
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
8768 Posts

Posted - 06/04/2012 :  08:43:19  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Go to Top of Page

sth_Weird
Starting Member

Germany
36 Posts

Posted - 06/06/2012 :  05:45:03  Show Profile  Reply with Quote
works great :)
thanx a lot!

sth_Weird
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 06/06/2012 :  06:56:48  Show Profile  Visit webfred's Homepage  Reply with Quote
welcome


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000