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.
| Author |
Topic |
|
ershad
Starting Member
3 Posts |
 Posted - 2005-10-15 : 18:44:35
|
| Hi,I have a table that needs to be refreshed periodically with new data. This new data may contain changes to existing rows and/or new rows that need to be inserted. If a row in the new data corresponds to an item that already exists in the table, an UPDATE needs to be performed; if the row's primary key does not exist in the table, an INSERT needs to be performed. There is a Merge statement in Oracle that combines the sequence of conditional INSERT and UPDATE commands in a single statement, depending on the existence of a record. I wonder how I can perform this in SQL-server in a clever way (SQL or T-SQL)? Below is an example of Oracles Merge statement.MERGE INTO SALES_FACT D USING SALES_JUL01 S ON (D.TIME_ID = S.TIME_ID AND D.STORE_ID = S.STORE_ID AND D.REGION_ID = S.REGION_ID) WHEN MATCHED THEN UPDATE SET d_parts = d_parts + s_parts, d_sales_amt = d_sales_amt + s_sales_amt, d_tax_amt = d_tax_amt + s_tax_amt, d_discount = d_discount + s_discount WHEN NOT MATCHED THEN INSERT (D.TIME_ID ,D.STORE_ID ,D.REGION_ID, D.PARTS ,D.SALES_AMT ,D.TAX_AMT ,D.DISCOUNT) VALUES ( S.TIME_ID ,S.STORE_ID ,S.REGION_ID, S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT);Thanks on before hand |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-10-15 : 23:07:19
|
| [code] UPDATE D -- Update anywhere the keys match SET d_parts = d_parts + s_parts, d_sales_amt = d_sales_amt + s_sales_amt, d_tax_amt = d_tax_amt + s_tax_amt, d_discount = d_discount + s_discount FROM SALES_FACT D INNER JOIN SALES_JUL01 S ON (D.TIME_ID = S.TIME_ID AND D.STORE_ID = S.STORE_ID AND D.REGION_ID = S.REGION_ID) INSERT INTO SALES_FACT (TIME_ID ,STORE_ID ,REGION_ID, PARTS ,SALES_AMT ,TAX_AMT ,DISCOUNT) SELECT S.TIME_ID ,S.STORE_ID ,S.REGION_ID, S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT); FROM SALES_FACT D RIGHT OUTER JOIN SALES_JUL01 S ON (D.TIME_ID = S.TIME_ID AND D.STORE_ID = S.STORE_ID AND D.REGION_ID = S.REGION_ID) WHERE D.TIME_ID IS NULL -- Anywhere a matching row in S is not found[/code] |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-16 : 01:54:03
|
| FWIW When we do this we also include a WHERE clause on the UPDATE that excludes any row that already has the correct value [in the target table] - cuts down the amount of UPDATEing and log space etc.Note that if you do that, and your database is case INsensitive, you may wish to compare varchar columns with a case SENSITIVE collation so that subtle capitalisation differences do cause an update.Kristen |
|
|
|
ershad
Starting Member
3 Posts |
Posted - 2005-10-16 : 03:15:19
|
| Hello Thanks for your answer. I haven’t tried it yet but it sure seems reasonable! |
|
|
|
|
|
|
|
|
Sequence of conditional INSERTs and/or UPDATEs