Primary Key conflict - SQL Server Forums

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

Primary Key conflict

Author

Topic

hubschrauber
Starting Member

16 Posts

Posted - 2005-11-30 : 04:39:17

All,

How do I solve a primary key conflict for a mutiple select statement.
When SQL finds a duplicate entry I doesn't ignore it like access but it doesn't insert anything.
It should be something like where not in.....

Thanks in advance

CREATE PROCEDURE [dbo].[z_qry_history_1_is]
AS
INSERT INTO r_tbl_history_is_rg ( ean_code, contractnr, switch_id, bakje, week, jaar )

SELECT distinct r_tbl_is_open.ean_code,
r_tbl_is_open.transaction_id,
r_tbl_is_open.switch_id,
r_tbl_is_open.bakje, datepart(ww,getdate()) AS Expr1, datepart(yyyy,getdate()) AS Expr2

FROM r_tbl_is_open;
GO

Violation of PRIMARY KEY constraint 'aaaaar_tbl_history_is_rg_PK'. Cannot insert duplicate key in object 'r_tbl_history_is_rg'.
The statement has been terminated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-30 : 04:47:29

check for exists in the where clause
for example :

insert into tbl1 (pk1, pk2, col1, col2)
select pk1, pk2, col1, col2
from tbl2 t
where not exists (select * from tbl1 x where x.pk1 = t.pk1 and x.pk2 = t.pk2)

-----------------
[KH]

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-11-30 : 04:47:36

try this as this wont include records which are already existing
INSERT INTO r_tbl_history_is_rg ( ean_code, contractnr, switch_id, bakje, week, jaar )

SELECT distinct r_tbl_is_open.ean_code,
r_tbl_is_open.transaction_id,
r_tbl_is_open.switch_id,
r_tbl_is_open.bakje, datepart(ww,getdate()) AS Expr1, datepart(yyyy,getdate()) AS Expr2
FROM r_tbl_is_open
where r_tbl_is_open.ean_code not in (select ean_code from r_tbl_history_is_rg )

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources