Search code examples
sqlsql-serverinsert-into

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

Solution

  • 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.