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.ThanksRaj |
|
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 likeupdate table1 set x=1,y=2,z=3 where a=1if @@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 updateUPDATE 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 updateUPDATE 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?MadhivananFailing 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 updateUPDATE 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 Table2UPDATE 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 NULLINSERT 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?MadhivananFailing to plan is Planning to fail
MySQLINSERT 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" |
|
|
|