T-SQL Enhancements in SQL Server 2008 phase-2

Hello,

I have described some features of sql server 2008 in my previous article. Now today i am going to describe merge statement.

Describe Merge :

  • Atomic statement combining INSERT, UPDATE and DELETE operations based on conditional logic
  • Done as a set-based operation; more efficient than multiple separate operations
  • MERGE is defined by ANSI SQL; you will find it in other database platforms as well
  • Useful in both OLTP(Online Transaction Processing) and Data Warehouse environments
    • OLTP: merging recent info from external source
    • DW: incremental updates of fact, slowly changing dimensions

Basic Syntax :

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>

OUTPUT Clause and $action Function :

  • Use OUTPUT clause to have the MERGE statement return effected rows
  • Use $action to return a string representing the action that modified the row (‘INSERT’, ‘UPDATE’, ‘DELERE’)

MERGE …
OUTPUT $action, deleted.*, inserted.*;

Example :

--Create a target table
CREATE TABLE Products
(
	ProductID INT PRIMARY KEY,
	ProductName VARCHAR(100),
	Rate MONEY
) 
GO

--Insert records into target table
INSERT INTO Products
VALUES
(1, 'Alu Tikki', 10.00),
(2, 'Cold Coffee', 20.00),
(3, 'Burger', 30.00),
(4, 'Pasta', 40.00)
GO

--Create source table
CREATE TABLE UpdatedProducts
(
	ProductID INT PRIMARY KEY,
	ProductName VARCHAR(100),
	Rate MONEY
) 
GO

--Insert records into source table
INSERT INTO UpdatedProducts
VALUES
(1, 'Alu Tikki', 10.00),
(2, 'Cold Coffee', 25.00),
(3, 'Burger', 35.00),
(5, 'Pizza', 60.00)
GO

SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO

MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
--When records are matched, update 
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 
OR TARGET.Rate <> SOURCE.Rate THEN 
UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
TARGET.Rate = SOURCE.Rate 
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN 
INSERT (ProductID, ProductName, Rate) 
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN 
DELETE
--$action specifies a column of type nvarchar(10) 
--in the OUTPUT clause that returns one of three 
--values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
--according to the action that was performed on that row
OUTPUT $action, 
DELETED.ProductID AS TargetProductID, 
DELETED.ProductName AS TargetProductName, 
DELETED.Rate AS TargetRate, 
INSERTED.ProductID AS SourceProductID, 
INSERTED.ProductName AS SourceProductName, 
INSERTED.Rate AS SourceRate; 
SELECT @@ROWCOUNT;
GO

SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO

Hope you enjoyed this; Now I will explain phase-3 in coming days.

T-SQL Enhancements in SQL Server 2008 phase-1

Hello,

we know that microsoft has released new version of sql server i.e. 2012 but developers are using old features in development.

I will show you development features of mssql2008 and mssql2012.

Today I am going to represent phase-1 of mssql 2008 development features.

T-SQL Miscellaneous

  • Declare and Initialize Variables
  • Compound Assignment Operators
  • Row Constructors
  • Composable DML

1. Declare and Initialize Variables

  • Can now declare and initialize variables in the same statement
  • Value is a self-contained expression of the target type (or at least implicitly convertible)

Example :-

DECLARE
   @i AS INT = 0,
   @s AS NVARCHAR(50) = (SELECT firstName FROM TableName WHERE employeeid = 1),
   @d AS DATETIME = CURRENT_TIMESTAMP;

2. Compound Assignment Operators

  • New assignment operators:
    +=, -=, *=, /=, %=

Example :-

UPDATE dbo.Products SET UnitPrice *= 2.05
-- Also try += 10, -= 10, /= 2, UnitsInStock %= 10

3. Row Constructors

  • VALUES clause returns relational table with multiple rows
  • Use with INSERT statement to insert multiple rows as an atomic operation:

Example :-

INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');

4. Composable DML

  • Combine multiple DML actions in one statement
  • Have one statement operate on rows affected by another
  • SQL Server 2008 introduces INSERT FROM DML:

INSERT INTO SELECT … FROM () AS D WHERE …;

Example :-

  • Update all products supplied by supplier 1;
  • increase the unit price by 15%
  • Audit only product updates that changed from below 20.0 to 20.0 or more
INSERT INTO dbo.ProductsAudit(ProductID, ColName, OldVal, NewVal)
SELECT ProductID, N'UnitPrice', OldVal, NewVal
FROM (UPDATE dbo.Products
SET UnitPrice *= 1.15
OUTPUT
inserted.ProductID,
deleted.UnitPrice AS OldVal,
inserted.UnitPrice AS NewVal
WHERE SupplierID = 1) AS D
WHERE OldVal < 20.0 AND NewVal >= 20.0;

That’s it. Now I will explain phase-2 in coming days.