t-sql insert into table if not exist

I've got 1 table

[Src Table]
  [ID No]
  [Date]
  [Selected]

[Dest Table]
  [ID No]
  [Date]
  [Value]
  [Selected]


I want to insert all the data from the Src Table to the dest table if the [ID No] and [Date] combination does not already exists in Dest Table.
koossaAsked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
There are a few ways to do it, but using a Not Exists clause like this will perform better than the others...

INSERT INTO [Dest Table] ([ID No], [Date], [Selected])
SELECT [ID No], [Date], [Selected]
FROM [Src Table] s
WHERE NOT EXISTS (SELECT 1 FROM [Dest Table] t
WHERE t. [ID No] = s.[ID No] and t.[Date]=s.[Date])
0
 
BurundiLappCommented:
insert into [dest table] (id no, etc..) select id no, etc.. from [src table] where [src table].[id no] NOT in (select [ID No] from [Dest Table]

The code isn't usable so you can't copy and paste but i hope you get the idea, let me know if you need fully working code.
0
 
SStoryConnect With a Mentor Commented:
INSERT INTO [Dest Table] d
([ID No], [Date],[Selected])
SELECT  s.[ID No], s.[Date], s.[Selected]
FROM [Src Table] s WHERE
 not exists (SELECT ID FROM [Src Table] s2 WHERE s2.ID=s.ID AND s2.Date=s.Date)

Try something like the above

You may have to put a d. in front of
([ID No], [Date],[Selected]) like this
(d.[ID No], d.[Date],d.[Selected])
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
SStoryCommented:
Guess we posted at the same time.
0
 
Ross TurnerManagement Information Support AnalystCommented:
I created and example script as sqlfiddle so you can have a play

http://sqlfiddle.com/#!3/261be/6


Create Table SRCTABLE
(IDNo int,
 Date varchar(30),
 Selected varchar(30))

INSERT INTO SRCTABLE(IDNo,Date,Selected) VALUES('1','16/12/2012 00:00:00','Test1');
INSERT INTO SRCTABLE(IDNo,Date,Selected) VALUES('2','15/12/2012 00:00:00','Test2');
INSERT INTO SRCTABLE(IDNo,Date,Selected) VALUES('3','31/05/2012 00:00:00','Test3');
INSERT INTO SRCTABLE(IDNo,Date,Selected) VALUES('4','31/05/2012 00:00:00','Test4');
INSERT INTO SRCTABLE(IDNo,Date,Selected) VALUES('5','23/08/2012 00:00:00','Test5');
INSERT INTO SRCTABLE(IDNo,Date,Selected) VALUES('6','21/04/2012 00:00:00','Test6');
INSERT INTO SRCTABLE(IDNo,Date,Selected) VALUES('7','27/02/2012 00:00:00','Test7');
INSERT INTO SRCTABLE(IDNo,Date,Selected) VALUES('8','21/09/2012 00:00:00','Test8');

Create Table DESTTABLE
(IDNo int,
 Date varchar(30),
 value int,
 Selected varchar(30))
  
  

INSERT INTO DESTTABLE(IDNo,Date,Value,Selected) VALUES('1','16/12/2012 00:00:00','456','Test1');
INSERT INTO DESTTABLE(IDNo,Date,Value,Selected) VALUES('2','15/12/2012 00:00:00','456','Test2');
INSERT INTO DESTTABLE(IDNo,Date,Value,Selected) VALUES('3','31/05/2012 00:00:00','456','Test3');
INSERT INTO DESTTABLE(IDNo,Date,Value,Selected) VALUES('4','31/05/2012 00:00:00','456','Test4');
INSERT INTO DESTTABLE(IDNo,Date,Value,Selected) VALUES('5','23/08/2012 00:00:00','456','Test5');

Open in new window



INSERT INTO DESTTABLE ([IDNo], [Date], [Selected])
SELECT [IDNo], [Date], [Selected]
FROM [SRCTABLE] st 

WHERE NOT EXISTS 

(SELECT * FROM [DESTTABLE] DT
WHERE DT. [IDNo] = st.[IDNo] and DT.[Date]=st.[Date]);

Select * from DESTTABLE

Open in new window

0
 
koossaAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.