T-SQl 2005 upsert 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)

T-SQl 2005 upsert help

Author

Topic

Rodgerga
Starting Member

3 Posts

Posted - 2009-04-17 : 14:22:35

Hi ,

Can any one help me with T-SQL correct syntax for this logic.

I wanted to upsert my destination(D) table from my staging(S).

The logic I want to follow is. I want it with EXISTS only, no INTERSECTION Please !

STEP 1: UPDATE destiantion table if a value exists in both staging(S) and destination(D).

UPDATE D
SET D.1=S.1
from S
where IFEXISTS(slect * ..................where S.id=D.ID) ????

STEP:2 DELETE all the rows that are common in both Staging and Destination from Staging.

Meaning, I have to delete all the values from stating table that I have update in my destination in my previous STEP.
??????

STEP 3: INSERT all the remaining rows from Staging(S) to Destination(D).

INSERT INTO D SELECT * from S (To do this I have to delete all my updated columns in my step 2 )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 14:30:11

use joins. also no need of deletion from stage. just use


--update records in dest from stage
UPDATE d
SET d.1=s.1
FROM D d
JOIN S s
ON d.ID=s.ID


--insert the others which are not already in dest

INSERT INTO D
SELECT s.*
FROM S s
LEFT JOIN D d
ON d.ID=s.ID
WHERE d.ID IS NULL

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-17 : 14:31:11

could this be of help in case it involves more than one field in the where S.id=D.ID

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123495

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-17 : 22:59:51

--insert the others which are not already in dest

INSERT INTO D
SELECT s.*
FROM S s where not exists (selct * from D d
where d.ID=s.ID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources