SQLTeam.com | Weblogs | Forums

Insert into table when record currently does not exist


#1

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


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


#2

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


#3

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