Primary key violation
-
Hi i need help on this.
I have two tables and
Table1 and Table 2
iam doing an insert and update from Table 2 into Table 1
Table 1 as the following columns
Firm,Id,Project
where Firm is the Primary Key
Table 2 as the following columns and has no primary key
Firm,test
i tried to an update and insert.
Business rule is : if record exists update it,if it does not exists insert into it
query:
insert into Table1 (Firm,id)
select Firm,test
from Table2
i get the following error when i run my query.
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'dbo.Table1'.
The statement has been terminated.
-
You are trying to add all the rows from table2, some of which have the same firm, or PK, in Table 1.
You need to do this as two statements.
update table1
set id = test
from table2
where table2.firm = table1.firm
then
insert Table1
select firm, test
from table2
where table2.firm not in (select firm from table1)
-
Something like...
DECLARE @Firm int, @test-2 int
DECLARE Table2_Cursor CURSOR FOR
SELECT Firm,test FROM Table2;
OPEN Table2_Cursor;
FETCH NEXT FROM Table2_Cursor
INTO @Firm, @test-2;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Table1
SET Id=@test
WHERE Firm=@Firm
IF (@@ROWCOUNT=0) AND (@@ERROR=0)
INSERT INTO Table1(Firm,Id)
VALUES(@Firm,@test)
FETCH NEXT FROM Table2_Cursor
INTO @Firm, @test-2;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
-
I vote for Steve's solution. You should not use a cursor for this.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Simon,
Why would you use a cursor for this??
Best,
Use Steve's solution or the same solution that you got the last time you posed this question here:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=354363
-
Using a cursor to do a simple "UPSERT"?? You an Oracle programmer, Simon?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I also vote for Steve's solution. But I would change the second statement using a left join:
insert INTO Table1
(Firm, ID)
select table2.firm, table2.test
from table2
LEFT JOIN table1
ON table2.firm = table1.firm
WHERE table1.firm IS NULL
🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply