Is there any UPSERT statement in SQL SERVER 2000

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 2000 Forums

SQL Server Development (2000)

Is there any UPSERT statement in SQL SERVER 2000

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2005-11-29 : 16:31:57

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

1421 Posts

Posted - 2005-11-29 : 16:34:09

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"

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-11-29 : 17:21:26

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

SamC
White Water Yakist

3467 Posts

Posted - 2005-11-29 : 18:46:25

[code]
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
[/code]

rkumar28
Starting Member

49 Posts

Posted - 2005-11-30 : 15:36:00

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-01 : 03:19:07

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

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 03:38:32

"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

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-01 : 16:13:25

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

moepMan
Starting Member

1 Post

Posted - 2009-02-25 : 03:14:26

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]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 03:34:29

And with SQL Server 2008, use the MERGE command.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources