CODEWITHSUNDEEP
sqlsql-serverinsert-into
MattR
MattR

Reputation: 5146

SQL Insert Into Where Record Not Exists

I have an automated process that appends data into Table1 every week. It only has 4 columns [Reason, Number of Occurences, Sent Date, and Program]. Table1 keeps growing and does not care about duplicate records. (There are only 26 Records each week and only 4 columns so performance/space is not an issue)

I have another table Table2 that I only want Distinct Records from Table1. If the record already exists in Table 2 I do not want to insert the record. I thought the below Statement would work, but it does not:

begin transaction
insert into [Database]..[Table2]
select Distinct * from [Database]..[Table1]
where not exists (select * from [Database]..[Table2])

(0 row(s) affected)

If I comment out the WHERE clause, it will work, but it will insert records that already exist in Table2

begin transaction
    insert into [Database]..[Table2]
    select Distinct * from [Database]..[Table1]
    --where not exists (select * from [Database]..[Table2])

(83 row(s) affected)

How do I check for Distinct Records in Table1 and if that record does not already exist in Table2, insert that record?

I am Using MS SQL Server Version 11.0.6020.0

Upvotes: 0

Views: 1387

Answers (3)

ChapL
ChapL

Reputation: 28

You need to add an extra WHERE clause in your inner select to compare table2 and table1 records : 

begin transaction
insert into [Database]..[Table2]
select Distinct * from [Database]..[Table1] t1
where not exists (
  select * from [Database]..[Table2] t2 
  where t1.reason=t2.reason 
    AND t1.occurences=t2.occurences 
    AND t1.sent_date=t2.sent_date 
    AND t1.program=t2.program
)

Upvotes: 0

Yogesh jain
Yogesh jain

Reputation: 1

I think you can use MERGE statement as well which will have only one condition WHEN NOT MATCHED BY TARGET then you will directly insert the row in target table.

Note: Please consider DISTINCT rows from Source table.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270883

In SQL Server, you would use except. Assuming the tables have the same columns:

insert into [Database]..[Table2]
    select Distinct t1.*
    from [Database]..[Table1] t1
    except
    select t2.*
    from [Database]..[Table2] t2;

Your not exists clause is not correlated to the data in table1, so it is not doing what you expect.

Upvotes: 4

Related Questions