November 20, 2009 at 4:53 pm
Hi All,
Here is my scenario.
I have two tables T1 (Sid int not null, Pid int not null) & T2(Sid int, Pid int).
T1 has composite primary key on (sid, pid)... Now I want to insert data from T2 to T1 making sure the data I am inserting in T1 does not exists there... Ofcourse If it exists it will fails due to composite primary key violation. However I don't want it to fail. I want to make sure before hand only when I insert data.
So basically, I want to check that the data in T2 table doesn't exist in T1 table first and if not then only insert it in table T1. OR insert only that data which doesn't exist in T1.
Thanks in advance for the help.
November 20, 2009 at 5:11 pm
apat (11/20/2009)
Hi All,Here is my scenario.
I have two tables T1 (Sid int not null, Pid int not null) & T2(Sid int, Pid int).
T1 has composite primary key on (sid, pid)... Now I want to insert data from T2 to T1 making sure the data I am inserting in T1 does not exists there... Ofcourse If it exists it will fails due to composite primary key violation. However I don't want it to fail. I want to make sure before hand only when I insert data.
So basically, I want to check that the data in T2 table doesn't exist in T1 table first and if not then only insert it in table T1. OR insert only that data which doesn't exist in T1.
Thanks in advance for the help.
Might not be the best trick but works.
Run the statement.
SELECT TOP 5 COUNT(*), Sid, Pid FROM T2
group by Sid, Pid
Having COUNT(*) > 1
If the statement returned at least 1 or more values, that indicates there are duplicates of that combination.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 20, 2009 at 5:15 pm
well I dont want to find duplicates from T2.
I want to find records from T2 which exists in T1 and make sure they are not inserted. Only those records which do not exist in T1 are supposed to be inserted.
November 20, 2009 at 6:00 pm
apat (11/20/2009)
well I dont want to find duplicates from T2.I want to find records from T2 which exists in T1 and make sure they are not inserted. Only those records which do not exist in T1 are supposed to be inserted.
Use this Script
INSERT INTO T1
SELECT Sid, Pid from T2
EXCEPT
SELECT Sid, Pid from T1
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 20, 2009 at 8:55 pm
Yes this works.. Thanks very much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply