April 17, 2009 at 12:21 pm
Upsert Logic
April 17, 2009 at 1:22 pm
Check the small demo bellow. I didn’t fallow the exact way that you asked, but if you want you can modify it to have also a delete statement before the insert statement and then do an insert without where clause.
--Creating the tables and insert some test data
create table tbl (i int not null primary key, c char(5))
go
insert into tbl (i,c)
select 1, 'abc'
union
select 2, 'def'
union
select 3, 'ghi'
go
create table StagingTbl (i int not null primary key, c char(5))
go
insert into StagingTbl (i,c)
select 3, 'ghijk'
union
select 4, 'lmnop'
go
--First step is to update column c according to i
--I do it with an update statement that uses
--from clause. You can read about it in BOL
update tbl
set tbl.c = StagingTbl.c
from tbl inner join StagingTbl on tbl.i = StagingTbl.i
--The insert is done with select that is doing a left
--join and inserts only records that were not found
--in tbl1.
insert into tbl (i,c)
select StagingTbl.i, StagingTbl.c
from StagingTbl left join tbl on StagingTbl.i = tbl.i
where tbl.i is null
go
--Check the results
select * from Tbl
--cleanup
drop table tbl
go
drop table StagingTbl
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 17, 2009 at 1:41 pm
A slightly easier, and generally accepted method is to perform a delete/insert instead of an upsert. For example:
DELETE FROM Destination
WHERE key IN (SELECT key FROM Source);
Or, using EXISTS:
DELETE FROM Destination
WHERE EXISTS (SELECT * FROM Source WHERE key = Destination.key);
Then, we perform the insert:
INSERT INTO Destination
SELECT {columns} FROM Source;
Once completed, cleanup the source...
DELETE FROM Source;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 19, 2009 at 8:11 am
Replacing the UPSERT with a DELETE then INSERT doesn't scale very well.
This is a demo of an alternative:
--DROP TABLE #Destination, #Staging
CREATE TABLE #Destination (a INT IDENTITY(1,1) PRIMARY KEY, Data VARCHAR(36) NULL)
CREATE TABLE #Staging (a INT IDENTITY(5,1) PRIMARY KEY, Data VARCHAR(36) NULL)
-- Test data
SET NOCOUNT ON
GO
INSERT #Destination (Data) SELECT '';
INSERT #Staging (Data) SELECT CONVERT(VARCHAR(36), NEWID());
GO 10
-- Show the 'before'
SELECT * FROM #Destination; SELECT * FROM #Staging
DECLARE@KeysInserted TABLE (a INT PRIMARY KEY)
SET IDENTITY_INSERT #Destination ON
-- Insert new rows, remembering the keys
INSERT#Destination (a, Data)
OUTPUTinserted.a INTO @KeysInserted
SELECTS.a, S.Data
FROM#Staging AS S
WHERENOT EXISTS (SELECT 1 FROM #Destination AS D WHERE S.a = D.a)
SET IDENTITY_INSERT #Destination OFF
-- Update
UPDATED
SETData = S.Data
FROM#Staging AS S
JOIN#Destination AS D ON D.a = S.a
WHERENOT EXISTS (SELECT 1 FROM @KeysInserted AS KI WHERE KI.a = S.a)
-- The 'after'
SELECT*
FROM#Destination
Cheers
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 19, 2009 at 5:14 pm
Hi ,
Thanks to you all for all your replis.
April 19, 2009 at 5:43 pm
Hi,
Several different solutions have been provided, all of which work - some even use EXISTS as you requested.
If you don't understand them, please try again. If you still don't get it, ask a specific question and you will get a specific answer.
"Plz post teh codez" is not gonna work here 🙂
Cheers
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2009 at 7:28 pm
Thanks, for your reply.
April 20, 2009 at 7:45 pm
All the information you need to answer question is in this thread, if you look for it.
We've done our bit by providing examples and explaining the techniques.
It's now up to you to put it into practice.
At least have go, based on what we've already given you, and come back after you've put some effort in, ok?
You won't learn new stuff by having it done for you all the way to the end.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2009 at 8:01 pm
rajamohangade (4/20/2009)
How can I write above logic by using EXISTS ?? Any help on this is really appreciated
[font="Verdana"]There are some excellent examples already posted. What have you tried? Let's have a look at the code you've written based on the examples so we can offer more help.[/font]
April 20, 2009 at 10:01 pm
Can you explain why the SQL Statement has to use the exist operator?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 21, 2009 at 12:18 am
Hi
April 21, 2009 at 3:09 am
rajamohangade (4/21/2009)
I am planning to use EXISTS for the sake of performance.
You should understand that EXISTS and the JOIN syntax are optimal choices in different situations.
Are you intending to test the relative performance of each? If so, how?
rajamohangade (4/21/2009)
Update Destination D
SETD.col2=S.col2,
D.col3=s.col3
From staging As S
where EXISTS(Select * from D where D.colx=S.colx and D.coly=S.coly)
You have the syntax wrong. Check the UPDATE FROM syntax in Books Online.
The general idea is you have to write:
UPDATE table_alias
SET ...statements...
FROM table_to_update AS table_alias -- Matches the UPDATE statement
WHERE EXISTS (SELECT 1 FROM table_to_check WHERE table_to_check.key_column = table_alias.key_column)
Books Online, a copy of developer edition SQL2K5, and a lot of hours spent trying things is the best way to master this stuff.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 21, 2009 at 9:08 am
Thank you!
April 21, 2009 at 11:09 am
Nope, the above loig in not working either . It giving an error Msg 4104, Level 16, State 1, Line 1
"UPDATE TestD
SET TestD.cola=testS.col1,
TestD.colb=testS.col2
FROM TestD
WHERE EXISTS(SELECT * FROM testS WHERE tesS.KEY1=TestD.key1)
"
April 21, 2009 at 11:39 am
James.N (4/17/2009)
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(select * ..................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 )
First of all, please don't tell us HOW you want it done, just tell us WHAT you want to accomplish. Don't limit yourself to a specific solution as it may not be the most performant solution available.
Second, if you really want good answers, please read the following article and follow the guidelines provided in posting DDL, sample data, expected results, and also the code you have currently tried to write: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
And for more information, read the following blog and the comments that follow: The Flip Side
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply