[Resolved] Insert using "where not exists"

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)

[Resolved] Insert using "where not exists"

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-12 : 16:43:12

Need to insert records into table1 with records from table2 if they do not already exist (in table1)

insert into #table1 (costcode, jdesumqty)
where not exists
(select jde_cost_code, jde_sum_qty
from #table2)

Error: Incorrect syntax near the keyword 'where'.

In table1 "costcode" is unique and in table2 "jde_cost_code" is unique

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 16:48:33

insert into #table1 (costcode, jdesumqty)
select distinct t2.jde_cost_code, t2.jde_sum_qty
from #table2 AS t2
where not exists (select * from #table1 AS t1 where t1.costcode = t2.jde_cost_code and t1.jdesumqty = t2.jde_sum_qty)

The distinct is essential for handling duplicates within #table2

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 23:36:58

or use left join

insert into #table1 (costcode, jdesumqty) 
select distinct t2.jde_cost_code, t2.jde_sum_qty
from #table2 AS t2
left join #table1 AS t1 
on t1.costcode = t2.jde_cost_code 
and t1.jdesumqty = t2.jde_sum_qty
where t1.costcode is null

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-13 : 05:02:09

You could also use EXCEPT to find differences:

SELECT *
FROM table1 where xxxxxx
except
select *
from table2
where xxxx

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-13 : 08:16:13

Thank you guys, they all worked, I was struggling with this ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources