How to update if exists else Insert in one SQL statement

[Karen Middleton]

In MS Access I can do in one SQL statement a update if exists else a
insert.

Assuming my source staging table is called - SOURCE and my target
table is called - DEST and both of them have the same structure as
follows

Keycolumns
==========
Material
Customer
Year

NonKeyColumns
=============
Sales
In Access I can do a update if the record exists else do a insert in
one update SQL statement as follows:
UPDATE DEST SET DEST.SALES = SOURCE.SALES
from DEST RIGHT OUTER JOIN SOURCE
ON (DEST.MATERIAL = SOURCE.MATERIAL AND
DEST.CUSTOMER = SOURCE.CUSTOMER AND
DEST.YEAR = SOURCE.YEAR)
This query will add a record in SOURCE into DEST if that record does
not exist in DEST else it does a update. This query however does not
work on SQL 2000

Am I missing something please share your views how I can do this in
SQL 2000.

Thanks
Karen

[Roji. P. Thomas]

You cannot do that in a Single statement in SQL Server.
You have to do Either

1.

IF EXISTS(SELECT 1 FROM DEST INNER JOIN SOURCE
ON (DEST.MATERIAL = SOURCE.MATERIAL AND
DEST.CUSTOMER = SOURCE.CUSTOMER AND
DEST.YEAR = SOURCE.YEAR)
--Update Statement
ELSE
--Insert Statement
OR

2.

UPDATE DEST SET DEST.SALES = SOURCE.SALES
from DEST RIGHT OUTER JOIN SOURCE
ON (DEST.MATERIAL = SOURCE.MATERIAL AND
DEST.CUSTOMER = SOURCE.CUSTOMER AND
DEST.YEAR = SOURCE.YEAR)
IF @@ROWCOUNT = 0
--Insert Statement

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Karen Middleton" <ka***********@yahoo.com> wrote in message
news:a5**************************@posting.google.c om...

In MS Access I can do in one SQL statement a update if exists else a
insert.

Assuming my source staging table is called - SOURCE and my target
table is called - DEST and both of them have the same structure as
follows

Keycolumns
==========
Material
Customer
Year

NonKeyColumns
=============
Sales
In Access I can do a update if the record exists else do a insert in
one update SQL statement as follows:
UPDATE DEST SET DEST.SALES = SOURCE.SALES
from DEST RIGHT OUTER JOIN SOURCE
ON (DEST.MATERIAL = SOURCE.MATERIAL AND
DEST.CUSTOMER = SOURCE.CUSTOMER AND
DEST.YEAR = SOURCE.YEAR)
This query will add a record in SOURCE into DEST if that record does
not exist in DEST else it does a update. This query however does not
work on SQL 2000

Am I missing something please share your views how I can do this in
SQL 2000.

Thanks
Karen

[Hugo Kornelis]

On 11 Nov 2004 00:42:37 -0800, Karen Middleton wrote:

In MS Access I can do in one SQL statement a update if exists else a
insert. (snip)This query will add a record in SOURCE into DEST if that record does
not exist in DEST else it does a update. This query however does not
work on SQL 2000

Am I missing something please share your views how I can do this in
SQL 2000.

Hi Karen,

SQL Server has no such feature. The best way to get the same result is
like this:

BEGIN TRANSACTION
UPDATE Dest
SET Sales = Source.Sales
FROM Dest
INNER JOIN Source
ON Source.Material = Dest.Material
AND Source.Customer = Dest.Customer
AND Source.Year = Dest.Year
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
-- Other error handling (generate message, log error, etc.)
GOTO Done
END
INSERT Dest (Material, Customer, Year, Sales)
SELECT Material, Customer, Year, Sales
FROM Source
WHERE NOT EXISTS
(SELECT *
FROM Dest
WHERE Source.Material = Dest.Material
AND Source.Customer = Dest.Customer
AND Source.Year = Dest.Year)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
-- Other error handling (generate message, log error, etc.)
GOTO Done
END
COMMIT TRANSACTION
Done:
PRINT 'Done'

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On Thu, 11 Nov 2004 14:36:26 +0530, Roji. P. Thomas wrote:

You cannot do that in a Single statement in SQL Server.
You have to do Either

1.

IF EXISTS(SELECT 1 FROM DEST INNER JOIN SOURCE
ON (DEST.MATERIAL = SOURCE.MATERIAL AND
DEST.CUSTOMER = SOURCE.CUSTOMER AND
DEST.YEAR = SOURCE.YEAR)
--Update Statement
ELSE
--Insert Statement
OR

2.

UPDATE DEST SET DEST.SALES = SOURCE.SALES
from DEST RIGHT OUTER JOIN SOURCE
ON (DEST.MATERIAL = SOURCE.MATERIAL AND
DEST.CUSTOMER = SOURCE.CUSTOMER AND
DEST.YEAR = SOURCE.YEAR)
IF @@ROWCOUNT = 0
--Insert Statement

Hi Roji,

These suggestions will only work as expected if only one row has to be
"upserted". For multi-row operation, option 2 without the IF condition is
(IMO) the only way to do this.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Roji. P. Thomas]

You are right. I was thinking abt the typical parameterized one row insert.
I realized my mistake only after seeing your first post.

Thanks for the correction

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:1d********************************@4ax.com...

On Thu, 11 Nov 2004 14:36:26 +0530, Roji. P. Thomas wrote:

You cannot do that in a Single statement in SQL Server.
You have to do Either

1.

IF EXISTS(SELECT 1 FROM DEST INNER JOIN SOURCE
ON (DEST.MATERIAL = SOURCE.MATERIAL AND
DEST.CUSTOMER = SOURCE.CUSTOMER AND
DEST.YEAR = SOURCE.YEAR)
--Update Statement
ELSE
--Insert Statement
OR

2.

UPDATE DEST SET DEST.SALES = SOURCE.SALES
from DEST RIGHT OUTER JOIN SOURCE
ON (DEST.MATERIAL = SOURCE.MATERIAL AND
DEST.CUSTOMER = SOURCE.CUSTOMER AND
DEST.YEAR = SOURCE.YEAR)
IF @@ROWCOUNT = 0
--Insert Statement

Hi Roji,

These suggestions will only work as expected if only one row has to be
"upserted". For multi-row operation, option 2 without the IF condition is
(IMO) the only way to do this.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Karen Middleton]

Hugo & Roji

Many thanks for your tips.

Hugo,

The solution you suggested is perfect but is it possible I can set
some kind of a commit interval for the update and Insert since my
staging table is 2 mill+ I do not want the temp db to bloat with that
size is it possible I can handle this in smaller chunks to optimally
use the tempdb space.

Also, is it possible I can make it generic so that I can pass the
source and target table names and can the stored procedure discover
all the key and non-key columns and do the upsert.

Thanks
Karen

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<qd********************************@4ax.com>. ..

On 11 Nov 2004 00:42:37 -0800, Karen Middleton wrote:

In MS Access I can do in one SQL statement a update if exists else a
insert.

(snip)

This query will add a record in SOURCE into DEST if that record does
not exist in DEST else it does a update. This query however does not
work on SQL 2000

Am I missing something please share your views how I can do this in
SQL 2000.

Hi Karen,

SQL Server has no such feature. The best way to get the same result is
like this:

BEGIN TRANSACTION
UPDATE Dest
SET Sales = Source.Sales
FROM Dest
INNER JOIN Source
ON Source.Material = Dest.Material
AND Source.Customer = Dest.Customer
AND Source.Year = Dest.Year
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
-- Other error handling (generate message, log error, etc.)
GOTO Done
END
INSERT Dest (Material, Customer, Year, Sales)
SELECT Material, Customer, Year, Sales
FROM Source
WHERE NOT EXISTS
(SELECT *
FROM Dest
WHERE Source.Material = Dest.Material
AND Source.Customer = Dest.Customer
AND Source.Year = Dest.Year)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
-- Other error handling (generate message, log error, etc.)
GOTO Done
END
COMMIT TRANSACTION
Done:
PRINT 'Done'

(untested)

Best, Hugo

[Hugo Kornelis]

On 11 Nov 2004 20:48:16 -0800, Karen Middleton wrote:

Hugo & Roji

Many thanks for your tips.

Hugo,

The solution you suggested is perfect but is it possible I can set
some kind of a commit interval for the update and Insert since my
staging table is 2 mill+ I do not want the temp db to bloat with that
size is it possible I can handle this in smaller chunks to optimally
use the tempdb space.
Hi Karen,

The usual pattern is something like this:

SET ROWCOUNT 10000 -- Adapt this to your needs
WHILE 1 = 1
BEGIN
UPDATE (or DELETE or INSERT)
SET ... = ...
WHERE ..... -- this must somehow exclude the rows
AND ..... -- that were handled in previous iterations
IF @@ROWCOUNT = 0 BREAK
END
SET ROWCOUNT 0

In your situation, you'd have to have two of these loops. The first for
updating existing rows, the second for inserting new rows. The second loop
is easy - if you insert 10,000 rows that don't already exist, the next
iteration will automatically skip these (as they now do exist) and go on
to the next 10,000. The update loop is harder - the only way to prevent
each iteration from simply updating the same set of rows is to compare not
only the primary key columns, but ALL columns - if all are equal, that row
should be excluded. That will result in a bloated query (depending on the
number of columns), with special handling for columns that may be NULL. It
will also be slow, as comparing all columns requires a table scan for the
correlated subquery (and if your staging table is 2 mill+ rows, your dest
table will probably be even bigger). Not nice.

Instead, I'd recommend you to use the following pattern:

CREATE TABLE #TempTable (-- same structure as staging table (Source),
-- constraints not required,
-- but add indexes to optimize for speed)
SET ROWCOUNT 10000 -- Adapt this to your needs
WHILE 1 = 1
BEGIN
BEGIN TRANSACTION
INSERT INTO #TempTable (..., ...)
SELECT ..., ...
FROM Source
ORDER BY ..., ... -- Must be a combination that has no duplicates
IF @@ROWCOUNT = 0 BREAK
DELETE FROM Source
ORDER BY ..., ... -- Same column combination as before
-- The order by ensures that the same (max 10000) rows get deleted that
-- were just copied into the #TempTable.
-- An alternative to using order by is to INSERT INTO 10,000 rows "at
-- random" (without ORDER BY) and use EXISTS to delete the same rows
-- from Source.
UPDATE Dest
SET Sales = #TempTable.Sales
FROM Dest
INNER JOIN #TempTable
ON #TempTable.Material = Dest.Material
AND #TempTable.Customer = Dest.Customer
AND #TempTable.Year = Dest.Year
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
-- Other error handling (generate message, log error, etc.)
GOTO Done
END
INSERT Dest (Material, Customer, Year, Sales)
SELECT Material, Customer, Year, Sales
FROM #TempTable
WHERE NOT EXISTS
(SELECT *
FROM Dest
WHERE #TempTable.Material = Dest.Material
AND #TempTable.Customer = Dest.Customer
AND #TempTable.Year = Dest.Year)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
-- Other error handling (generate message, log error, etc.)
GOTO Done
END
COMMIT TRANSACTION
END
-- After WHILE is exited through BREAK, a transaction is still open!
COMMIT TRANSACTION
Done:
PRINT 'Done'

Also, is it possible I can make it generic so that I can pass the
source and target table names and can the stored procedure discover
all the key and non-key columns and do the upsert.

It is possible using dynamic SQL, but I recommend strongly against it. It
does save you some copy, change, paste work when making your application,
but it will probably cost you lots more work in the long run. If I have to
do something like this, I'd prefer to have seperate "upsert" procedures
for each table that needs this functionality. Once you got one working
procedure, it's easy to make more: use copy and paste, change the names
(using find and replace), proofread and test and you're set.

To read more about dynamic SQL: http://www.sommarskog.se/dynamic_sql.html.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)