SQL Server Performance

Insert Data in a table having composite primary key???

Discussion in 'SQL Server 2008 General Developer Questions' started by apat, Nov 20, 2009.

  1. apat New Member

    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.
  2. madhuottapalam New Member

    Check this
    create
    table t1 (Sid int not null, Pid int not null) create
    table T2(Sid int, Pid int) insert
    t1 select 1,1insert
    t1 select 2,2insert
    t1 select 3,3insert t1 select 4,6 insert
    t2 select 4,5insert
    t2 select 1,1insert
    t2 select 2,2insert
    t2 select 4,4
    -- Method 1insert
    into t1 select *From t2 a where
    not exists(select * from t1 b where a.sid=b.sid and a.pid=b.pid)--Method 2
    insert
    into t1 select a.*From t2 a left outer join t1 b
    on a.sid=b.sid and a.pid=b.pid where
    b.sid is null and b.pid is null

Share This Page