distinct rows query help - 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)

distinct rows query help

Author

Topic

DesiGal
Starting Member

31 Posts

Posted - 2010-03-03 : 14:24:46

I have two tables
Demos(DemoId,DemoName,MakeId,TypeId)
Test(DemoName,MakeId,TypeId)

i am trying to insert rows from Test table in to my Demos table, only those rows where(DemoName,MakeId,TypeId)combination does not exists in the Demos table.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-03 : 14:33:01

Here's one way...you can use a LEFT JOIN

INSERT INTO Demos
(DemoName,MakeId,TypeId)
SELECT    t1.* 
FROM  Test t1 
WHERE NOT EXISTS (SELECT * FROM Demos t2 WHERE t1.DemoName = t2.DemoName AND t1.MakeId = t2.MakeId AND t1.TypeId = t2.TypeId)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-03 : 14:44:12

quote:
Originally posted by DesiGal

I have two tables
Demos(DemoId,DemoName,MakeId,TypeId)
Test(DemoName,MakeId,TypeId)

i am trying to insert rows from Test table in to my Demos table, only those rows where(DemoName,MakeId,TypeId)combination does not exists in the Demos table.

Are your columns nullable?

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-03 : 14:50:09

using set operators are very nice and easy.
you can use if too.

INSERT INTO Demos(DemoName,MakeId,TypeId)
SELECT DemoName, MakeId, TypeId
  FROM  Test 
EXCEPT  
SELECT DemoName, MakeId, TypeId
  FROM Demos

DesiGal
Starting Member

31 Posts

Posted - 2010-03-03 : 14:59:38

Thanks for the reply guys both the queries worked for me .

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-03 : 15:04:00

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources