| Author |
 Topic  |
|
|
rkumar28
Starting Member
USA
49 Posts |
 Posted - 11/29/2005 : 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
USA
1414 Posts |
Posted - 11/29/2005 : 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
USA
525 Posts |
Posted - 11/29/2005 : 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
USA
3459 Posts |
Posted - 11/29/2005 : 18:46:25
|
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
|
|
|
|
rkumar28
Starting Member
USA
49 Posts |
Posted - 11/30/2005 : 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
India
22460 Posts |
Posted - 12/01/2005 : 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
United Kingdom
22191 Posts |
Posted - 12/01/2005 : 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
USA
3459 Posts |
Posted - 12/01/2005 : 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 Posts |
Posted - 02/25/2009 : 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
Sweden
29138 Posts |
Posted - 02/25/2009 : 03:34:29
|
And with SQL Server 2008, use the MERGE command.
E 12°55'05.63"
N 56°04'39.26" |
|
|
| |
Topic |
|
Is there any UPSERT statement in SQL SERVER 2000
