One of the fantastic new features of SQL Server 2008 is Merge Statement. Using a single statement, we can Add/Update records in our database table, without explicitly checking for the existence of records to perform operations like Insert or Update.
Here are a few facts that you must know before starting to use Merge Statement:
INSERT, UPDATE and DELETE operations based on conditional logic MERGE is defined by ANSI SQL; you will find it in other database platforms as well A typical merge statement looks like:
MERGE [INTO] <target table>
USING <source table or table expression>
ON <join/merge predicate> (semantics similar to outer join)
WHEN MATCHED <statement to run when match found in target>
WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>
-- Update existing, add missing
MERGE INTO dbo.tbl_Customers AS C
USING dbo.tbl_CustomersTemp AS CT
ON C.CustID = CT.CustID
WHEN MATCHED THEN
UPDATE SET
C.CompanyName = CT.CompanyName,
C.Phone = CT.Phone
WHEN NOT MATCHED THEN
INSERT (CustID, CompanyName, Phone)
VALUES (CT.CustID, CT.CompanyName, CT.Phone)
CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);
CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);
--Synchronize source data with target
MERGE INTO dbo.tbl_Target AS t
USING dbo.tbl_Source AS s
ON t.id = s.id
WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN
--Row exists and data is different
UPDATE SET t.name = s.name, t.qty = s.qty
WHEN NOT MATCHED THEN
--Row exists in source but not in target
INSERT VALUES (s.id, s.name, s.qty)
WHEN SOURCE NOT MATCHED THEN
--Row exists in target but not in source
DELETE OUTPUT$action, inserted.id, deleted.id
So now with this new feature, we can implement the feature of add/insert/delete using a single statement without checking through the records.
Hope you enjoyed this article. Happy programming!!!
| You must Sign In to use this message board. | |||||||||||
|
|||||||||||
|
|||||||||||
|
|||||||||||
