473,836 Members | 1,422 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to update if exists else Insert in one SQL statement

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
Jul 20 '05 #1
6 61588
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.goo gle.com...
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

Jul 20 '05 #2
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)
Jul 20 '05 #3
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)
Jul 20 '05 #4
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_rFa ct.in_SPAM_fo> wrote in message
news:1d******** *************** *********@4ax.c om...
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)

Jul 20 '05 #5
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_rFa ct.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

Jul 20 '05 #6
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.Sale s
FROM Dest
INNER JOIN #TempTable
ON #TempTable.Mate rial = Dest.Material
AND #TempTable.Cust omer = 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.Mate rial = Dest.Material
AND #TempTable.Cust omer = 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)
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4543
by: newbie_mw | last post by:
Seems my post was buried in more cries for help :-) I will try again. It's probably a very novice question so please take a look! Thanks! ----------------------------------------------------------------- I created a sign-up sheet (reg.html) where people fill in their first name, last name, email, etc. The data are then sent to a PHP script (reg.php). The data are then inserted into a table (reg) in MS SQL server. I have declared the...
16
3882
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the table, or perhaps bytes, being updated where the engine just decides, screw it, i'll just make a new one. surfed this group and google, but couldn't find anything. the context: we have some java folk who like to parametize/
10
3407
by: Mike | last post by:
I know this sounds strange but I am at a loss. I am calling a simple funtion that opens a connection to a SQL Server 2000 database and executes an Insert Statement. private void AddMinimunWageStipen(string payrollid,double amount) { System.Data.SqlClient.SqlConnection cn = null; System.Data.SqlClient.SqlCommand cm = null;
1
1614
by: Scott Emick | last post by:
I have the following datatables which are related: Transaction TransactionId-Sequence Orders OrderDetails when I process a daTransaction.update(dtTransaction) daTransactionIdSequence.update(dtTransactionIdSequence)
1
3089
by: glenn | last post by:
Hi folks, I am using an Access database, VB.NET and ADO.NET working with a DataGrid control. MY datagrid table has both a date_sent and a date_ans field. When I Insert a record in my SQL statement, I insert a date_sent but I need to insert a blank date_ans field. date_sent is the date when a question was sent to a group from a web form and date_ans is the date when a question was answered.
3
1832
by: rhaazy | last post by:
Using ms sql 2000 I have 2 tables. I have a table which has information regarding a computer scan. Each record in this table has a column called MAC which is the unique ID for each Scan. The table in question holds the various scan results of every scan from different computers. I have an insert statement that works however I am having troulbe getting and update statement out of it, not sure if I'm using the correct method to insert...
3
1622
by: MATTXtwo | last post by:
ALTER PROCEDURE . ( @user_id varchar(8), @pwd varchar(12) ) AS IF EXISTS(SELECT Personel_No From TblUser_ID where Personel_No = @user_id) BEGIN IF EXISTS(SELECT Password From TblUser_ID where Personel_No = @user_id AND Password = @pwd)
2
7341
by: ksenthilbabu | last post by:
Hey All, I am using MSSQL -2005 with VB6. I have created a master table tblCompany and detail Table tblDetail having foreign key relationship. When i try to insert a value within a TRANSACTION I am getting Error No. -2147217873 at Line No. 0 (The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblDetail_tblCompany". The conflict occurred in database "DBTest", table "dbo.tblCompany", column RefID.) . please help me to solve...
5
2378
Privesh Baliper
by: Privesh Baliper | last post by:
Hi I have two tables with the same structur: called DISPLAYLOAYOUT and DISPLAYLAOUT1 : CREATE TABLE CARDBA.DISPLAYLAYOUT - DL ( AREACODE VARCHAR2(5 BYTE) NOT NULL, ZONECODE VARCHAR2(5 BYTE) NOT NULL, LOCATIONCODE VARCHAR2(5 BYTE) NOT NULL, RECTORIGINX NUMBER,
0
9825
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10558
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10257
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6981
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4459
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.