if exists, update... if not, insert

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 2000 Forums

SQL Server Development (2000)

if exists, update... if not, insert

Author

Topic

KimJ
Starting Member

38 Posts

Posted - 2005-01-28 : 11:36:12

Hi -

I hope this makes sense. I'm looking for the best way to handle this situation...

I have 2 databases (linked servers - both sql 2000). I need to check for the existence of a record in one table and if it exists, either leave it alone or update a field based on certain rules.

If the record doesn't exist, then insert it.

Each of these actions alone are no problem, but I'm not sure how to combine them.

So, based on select of table2: if record exists in table1, check for value of field1 and update field1 value if necessary. If record doesn't exist, insert it.

I'd like to do this in a DTS Package, as the table will need to be updated nightly.

Any suggestions? I'd like to avoid a cursor, as there can be thousands of rows to get through and I think it would take too long.

Thanks in advance,
Kim

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-28 : 12:18:16

UPDATE and INSERT are seperate DML statements and cannot be combined.


update t1
set <whatever>
from table1 t1 inner join table2 t2 on <join condition>

insert t1(<column list>)
select <column list>
from table2 t2
where not exists (
    select 1
    from table1
    where <join condition>)

Did you know you can create jobs that run nightly and have steps that don't execute DTS Packages?

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources