SQLTeam.com | Weblogs | Forums

Insert only not exists data

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

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
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
    )

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.

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

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