Not Exist in SQL Server 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Not Exist in SQL Server 2005

Author

Topic

roshana
Starting Member

31 Posts

Posted - 2009-10-13 : 00:29:56

Hello All,

I have one table with a composite primary key --> Table1
Table1

Customer ID
SOP Number Primary Key
Sequence No Primary Key
Item No
I have one more table with the same structure --> DupTable1
DupTable1

Customer ID
SOP Number
Sequence No
Item No

I have 25 rows in Table1 and 10 rows in the DupTable1 .I have to insert rows into the DupTable1 which is not there in the DupTable1 table from Table1 (balance 15 rows in the DupTable1 (25 rows from Table1 - 10DupTable1 ) ).How can I use not Exist in SQL server 2005 to Insert the above situation

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-10-13 : 00:38:35

One way is to use a left join to identify the rows that do not exist.

insert into DupTable(CustomerId, SOPNumber, ...)
    select t1.CustomerId, t1.SOPNumber,...
    from   Table1 t1
    left
    join   DupTable dt on
           t1.SOPNumber = dt.SOPNumber and
           t1.SequenceNo = dt.SequenceNo
    where  dt.SOPNumber is null

roshana
Starting Member

31 Posts

Posted - 2009-10-13 : 01:10:15

Thanks Nathans
This is working fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources