SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Is there any UPSERT statement in SQL SERVER 2000
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rkumar28
Starting Member

USA
49 Posts

Posted - 11/29/2005 :  16:31:57  Show Profile  Reply with Quote
Hello,
I am trying to do an Upsert(Update the record if it exists else Insert the record).
I have TABLE1 that has 9 columns and TABLE2 that has 15 columns. I have to insert records that from TABLE2 to TABLE1.

If the records already exists I want to UPDATE TABLE1 with latest records from TABLE2.
If the records does not exists in TABLE1, I want to INSERT the record.


IS THERE ANY UPSERT STATMENT IN SQL SERVER 2000.

Thanks

Raj

mfemenel
Professor Frink

USA
1421 Posts

Posted - 11/29/2005 :  16:34:09  Show Profile  Visit mfemenel's Homepage  Reply with Quote
Read up on if exists(). The concept of "upsert" doesn't exist but you can use if exists to figure out if the row is there or not.

Mike
"oh, that monkey is going to pay"
Go to Top of Page

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 11/29/2005 :  17:21:26  Show Profile  Send aiken an ICQ Message  Reply with Quote
Depending on the specific thing you're trying to do, you might also want to do something like

update table1 set x=1,y=2,z=3 where a=1
if @@ROWCOUNT=0
   insert into table2 (x,y,z) values (1,2,3)


...it's a little cheaper than always doing an "if exists" followed by either an insert or update.

Cheers
-b
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 11/29/2005 :  18:46:25  Show Profile  Reply with Quote

INSERT INTO Table1 (Col list here)
 SELECT Column list
   FROM Table2 T2
   LEFT OUTER JOIN Table1 T1 ON Join condition here
   WHERE T1.Column IS NULL -- If no match, insert this row
-- Now do an update
UPDATE T1
  SET  --  You know the column names, not me !
  FROM Table2 T2
  INNER JOIN Table1 T1 ON join condition here
Go to Top of Page

rkumar28
Starting Member

USA
49 Posts

Posted - 11/30/2005 :  15:36:00  Show Profile  Reply with Quote
quote:
Originally posted by SamC


INSERT INTO Table1 (Col list here)
 SELECT Column list
   FROM Table2 T2
   LEFT OUTER JOIN Table1 T1 ON Join condition here
   WHERE T1.Column IS NULL -- If no match, insert this row
-- Now do an update
UPDATE T1
  SET  --  You know the column names, not me !
  FROM Table2 T2
  INNER JOIN Table1 T1 ON join condition here




Thanks a bunch. I implemented the suggestion above and it worked perfectly.

Raj
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 12/01/2005 :  03:19:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
In SQL Server, there is no single statement that does both Update and Insert. You need to do them seperately as suggested. Is this possible in any other DBMS?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/01/2005 :  03:38:32  Show Profile  Reply with Quote
"INSERT INTO Table1 (Col list here)
...
-- Now do an update
UPDATE T1
...
"

I suggest you might do the UPDATE first, that will save you having to re-update the rows you just inserted!

Kristen
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 12/01/2005 :  16:13:25  Show Profile  Reply with Quote
Depending on the number of rows, size of DB, indexes, sun, moon and barometric pressure, it might be faster to UPDATE then INSERT.

-- Update any columns in Table1 that match Table2
UPDATE T1
  SET  --  You know the column names, not me !
  FROM Table2 T2
  INNER JOIN Table1 T1 ON join condition here
  WHERE T1.Column IS NOT NULL


INSERT INTO Table1 (Col list here)
 SELECT Column list
   FROM Table2 T2
   LEFT OUTER JOIN Table1 T1 ON Join condition here
   WHERE T1.Column IS NULL -- If no match, insert this row
Go to Top of Page

moepMan
Starting Member

1 Posts

Posted - 02/25/2009 :  03:14:26  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

In SQL Server, there is no single statement that does both Update and Insert. You need to do them seperately as suggested. Is this possible in any other DBMS?

Madhivanan

Failing to plan is Planning to fail



MySQL

INSERT INTO foo VALUES (bar, bar) ON DUPLICATE KEY UPDATE x = bar, y= bar;

"THERE IS NO JUSTICE, THERE IS JUST ME" [Death, Terry Pratchett]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30187 Posts

Posted - 02/25/2009 :  03:34:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And with SQL Server 2008, use the MERGE command.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000