If Not Exists - How To? 1

[RJL1]

I have a query that returns the field I need to insert into a separate table. This is working fine and my current test data inserts 3 records into my new table. However if I run the query again I insert the same 3 records.

I like to have a IF NOT EXISTS selection right before the insert to prevent records already added into the new table (tblRecount). My criteria would be to insert any records that does not match a combination of 3 fields. So if combination of Item, Location and Count_1 fields already exist in the new table (tblRecount) then do not insert a the duplicate record.

Fields in new table

CONSOLE_ID
ITEM
LOCATION
ILS
COUNT_1
DIFF
USER_NAME
DATE_TIME
RECOUNT

Any help is appreciated
RJL1

 

[bborissov]

INSERT INTO NewTable (put field list here)
SELECT (put field list here)
FROM OldTable
LEFT JOIN NewTable 
     ON OldTable.Item     = NewTable.Item     AND
        OldTable.Location = NewTable.Location AND
        OldTable.Count    = NewTable.Count
WHERE NewTable.Item IS NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

You could probably re-work this to use If Not Exists, but I would approach it a different way.

What I would do is base your insert off a join between your old table and the new table. If you use a left join and check for NULL, you will get data inserted if it does not exist, and NOT inserted if it does exist.

The query I show below is a rough draft of my thinking process. It was NOT tested, and may even contain syntax errors. However, if you understand the thinking process, it shouldn't be too difficult to adapt this to your code.

Insert 
Into   NewTable(
         CONSOLE_ID,ITEM,LOCATION,ILS,COUNT_1,
         DIFF,USER_NAME,DATE_TIME,RECOUNT)
Select OldTable.CONSOLE_ID,
       OldTable.ITEM,
       OldTable.LOCATION,
       OldTable.ILS,
       OldTable.COUNT_1,
       OldTable.DIFF,
       OldTable.USER_NAME,
       OldTable.DATE_TIME,
       OldTable.RECOUNT
From   OldTable
       Left Join NewTable
         On  OldTable.Item = NewTable.Item
         And OldTable.Location = NewTable.Location
         And OldTable.Count_1 = NewTable.Count_1
Where  NewTable.Item Is NULL

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Nice code Boris.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

I'm too lazy to put the actual field list )))))))))))))))))

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

In addition to the above 2 responses, take a look at this thread

http://social.msdn.microsoft.com/Fo...l/thread/f0800dc4-25a7-4da4-83cc-aa89980a8fd7

PluralSight Learning Library

 

[RJL1]

Thanks for all the replies. I used bborissov code and worked like a charm. very flexible if I need to add ortake away criteria.

Thanks
RJL1

 

[markros]

Gail Shaw recently measured the performance of LEFT JOIN vs. NOT EXISTS and found NOT EXISTS to perform better

http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

PluralSight Learning Library