INSERT (NOT IN) - 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

SQL Server 2000 Forums

Transact-SQL (2000)

INSERT (NOT IN)

Author

Topic

bass28
Starting Member

4 Posts

Posted - 2004-09-20 : 10:48:57

I wrote the following statement to attempt to insert item_no into the BM_StdCost table only when there is not alreay a row for item_no.

INSERT BM_StdCost (item_no)
SELECT BM.comp_item_no
FROM BM_FlatBOM BM
WHERE BM.username = @username
AND BM.comp_item_no NOT IN (SELECT BM_StdCost.item_no
FROM BM_StdCost);

The INSERT attempt to insert all rows from BM_FlatBOM even if there is already an entry in BM_StdCost. What am I doing wrong?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-20 : 10:50:24

INSERT BM_StdCost (item_no)
SELECT BM.comp_item_no
FROM BM_FlatBOM BM
Left Join BM_StdCost SC
On BM.comp_item_no = SC.item_no
WHERE BM.username = @username
and SC.item_no is null

Corey

bass28
Starting Member

4 Posts

Posted - 2004-09-20 : 11:32:59

Thanks Seventhnight.

I changed your suggestion to include the DISTINCT keyword before BM_StdCost.comp_item_no since BM_Std_Cost could have duplicate item_nos and that works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources