SQLTeam.com | Weblogs | Forums

Insert into table when record currently does not exist


Hi Guys,

Can somebody fix this query i want to append data only if the current table does not already contain that record.

Only sasha from the Extract table should be appended into test table

drop table test

create table test(Text1 varchar(255),date1 datetime)

insert into test values('Ryu','20150101'),('Ken','20140101'),('Sara','20150101')

drop table [extract]

select *
into [extract]
from (
select 'Sara' as Text1 ,convert(datetime,'20150101') as date1
union all
select 'Sasha' as Text1 ,convert(datetime,'20140101') as date1

insert into test
select * from [extract] where not exists (select * from test)


INSERT INTO Test (Text1, date1)
SELECT Text1, date1
FROM [Extract]
WHERE NOT EXISTS (SELECT * FROM Test WHERE Test.Text1 = Extract.Text1);

There are many ways to do this but I just used your code.


thank you that worked but out of the other methods is this the most simplest ?