Insert only not exists data

1

Hi ,
I have 2 tables both have the same fields let says table a and table b, I need to add record from table b into table a but only not found data on table a. What is the syntax for those case?

thanks,

Joe

2

insert into b(f1, f2, f3)
select a.f1, a.f2, a.f2
from a
left join b
on a.key = b.key
where b.key is null

1 Like
3 
INSERT INTO dbo.tablea ( col1, col2, col3 )
SELECT col1, col2, col3
FROM dbo.tableb b
WHERE NOT EXISTS(
    SELECT 1
    FROM dbo.tablea a
    WHERE a.key_col = b.key_col
    )
4

As an alternative and provided the Clustered Index isn't based on something like an IDENTITY column, you could also create the Clustered Index with the IGNORE_DUP_KEY option turned on. Rows that don't exist with that key would be inserted and rows that do exist already would simply not be inserted.

5

Another idea is MERGE statement

But it might be a little TOO much

what it does .. if exists update .. if not exists insert !!!

https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

6

You don't have to tell MERGE to do an update. It'll work fine for either or both.