How to inser unique record into a table?
Table A has a primary key in MemberID. I used code below but got error said

Violation of PRIMARY KEY constraint 'PK_ID'. Cannot insert duplicate key in
object 'A'

INSERT INTO A(MemberID)
SELECT MEMBID FROM B
where B.MEMBID not in (select MemberID from A)

Does B have duplicate MEMBID's.  If so, that can cause this error.

Tom

news:98d2c9484d598@uwe...
Show quoteHide quote

Tom answered what could be the problem, you can fix it like this:

INSERT INTO A(memberid)
SELECT DISTINCT membid
FROM B
WHERE NOT EXISTS(SELECT *
                  FROM A
                  WHERE A.memberid = B.membid);

or this:

INSERT INTO A(memberid)
SELECT membid FROM B
EXCEPT
SELECT memberid FROM A;

news:98d2c9484d598@uwe...
Show quoteHide quoteYour B table may have multiple B.MEMBERIDs that are the same.

Try the following simple modification to your script:

INSERT INTO A(MemberID)
SELECT DISTINCT MEMBERID FROM B
where B.MEMBERID not in (select MemberID from A)


--  A better method is to use an EXISTS clause or a  join rather than the IN
statement.
INSERT INTO A(MemberID)
SELECT DISTINCT MemberID FROM b
WHERE NOT EXISTS (SELECT 1 FROM A JOIN B ON A.MEMBERID = B.MEMBERID)

-- Or in 2005, you can use an EXCEPT clause as well
INSERT INTO A(MemberID)
SELECT    DISTINCT MemberID FROM B
EXCEPT
SELECT MemberID FROM A


I hope this helps.

Rick Sawtell
MCT, MCSD, MCDBA, MCITPro

report on indexes on foreign keys
SQL cannot connect to database for OnePoint/MOM/ForeFront all of the sudden???
using profiler to log master.sysprocesses entries ?
Best Practices - using params passed to stored procedure
SQL 2k and autonumbering
Exception Access Violation in SQL
Growing log file issue
Log Shipping and extremely large .wrk file
Shrinking transaction logs
SQl Server Reporting Services - migrating 2000 to 2008