koossa
asked on
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.
[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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Guess we posted at the same time.
I created and example script as sqlfiddle so you can have a play
http://sqlfiddle.com/#!3/261be/6
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');
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
ASKER
Thank you
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.