Jag fick frågan om jag inte kunde publicera mina Powerpoint-bilder + SQL 2008-exempel när jag körde lanseringsevent i Malmö igår tillsammans med Magnus Mårtensson från Dotway.
Powerpoint-presentationerna har jag tidigare lagt upp här.
Här kommer de SQL-demos som jag visade - först exemplet på den nya MERGE-funktionen (du kan även ladda hem .sql-filerna direkt här). I nedanstående kod finns även ett exempel på hur du kan radera rader som finns i mål-tabellen men inte i käll-tabellen, något som jag inte visade i min demonstration:
USE tempdb
go
/*
DROP TABLE Customers;
DROP TABLE QuarterlyUpdate;
*/
--
-- Step 1: Create and Populate Customers table.
-- Note: Population uses row constructors, which are new in SQL Server 2008
--
CREATE TABLE Customers (
customer_id INT IDENTITY PRIMARY KEY,
customer_name VARCHAR(30),
policy_type INT CHECK(policy_type > 0 AND policy_type < 4),
deductable MONEY,
number_of_claims INT
)
INSERT Customers VALUES
('Robert Smith', 1, 1000, 0),
('Mary Johnson', 1, 5000, 0),
('Fred Paulson', 2, 2000, 1),
('Nancy Jones', 2, 1000, 1)
GO
SELECT * FROM Customers
GO
--
-- Step 2: Create and Populate QuarterlyUpdate table.
-- Note: Population uses row constructors, which are new in SQL Server 2008
-- Note: Not every Customer must appear in the QuarterlyUpdate table.
--
CREATE TABLE QuarterlyUpdate (
customer_id INT,
customer_name VARCHAR(30),
policy_type INT CHECK(policy_type > 0 AND policy_type < 4),
deductable MONEY,
new_claims INT
)
GO
INSERT QuarterlyUpdate VALUES
(1, 'Robert Jones', 1, 1000, 0),
(2, 'Mary Johnson', 1, 5000, 2),
(0, 'Katy Fredericks', 2, 2000, 0), -- New Customer
(0, 'Phil Fredericks', 2, 2000, 0) -- New Customer
GO
SELECT * FROM Customers
SELECT * FROM QuarterlyUpdate
GO
--
-- Step 3: MERGE
-- Note: No action is take when row appears in Customer but not QuerterlyUpdate
-- Note: Uses increment operator, new in SQL Server 2008 T-SQL
--
MERGE Customers C
USING QuarterlyUpdate Q
ON C.customer_id = Q.customer_id
WHEN MATCHED THEN
UPDATE SET C.customer_name = Q.customer_name,
C.policy_type = Q.policy_type,
C.deductable = Q.deductable,
C.number_of_claims += new_claims
WHEN NOT MATCHED THEN
INSERT VALUES(customer_name, policy_type, deductable, new_claims)
OUTPUT $action, INSERTED.customer_id,
INSERTED.customer_name as [New Name],
INSERTED.policy_type as [New Policy],
INSERTED.deductable as [New Deductable],
INSERTED.number_of_claims as [New Number Of Claims],
DELETED.customer_name as [Original Name],
DELETED.policy_type as [Original Policy],
DELETED.deductable as [Original Deductable],
DELETED.number_of_claims as [Original Number Of Claims];
GO
DELETE QuarterlyUpdate;
--
-- Step 5: A row for each current customer is required in QuarterlyUpdate
-- If no row is present, customer will be deleted
--
INSERT QuarterlyUpdate VALUES
(1, 'Robert Jones', 1, 1000, 0),
(2, 'Mary Johnson', 1, 5000, 2),
(3, 'Fred Paulson', 2, 2000, 1),
(5, 'Katy Fredericks', 2, 2000, 1),
(6, 'Phil Fredericks', 2, 2000, 0),
(0, 'Sam Fielding', 3, 10000, 0)
GO
--
-- Step 3: MERGE
-- Note: When row appears in Customer but not QuerterlyUpdate, Customer is deleted
-- Note: Uses increment operator, new in SQL Server 2008 T-SQL
-- Note: Uses WHEN SOURCE NOT MATCHED - keyword SOURCE is required
-- and Uses WHEN TARGET NOT MATCHED - keyword TARGET is optional
--
MERGE Customers C
USING QuarterlyUpdate Q
ON C.customer_id = Q.customer_id
WHEN MATCHED THEN
UPDATE SET C.customer_name = Q.customer_name,
C.policy_type = Q.policy_type,
C.deductable = Q.deductable,
C.number_of_claims += new_claims
WHEN SOURCE NOT MATCHED THEN
DELETE
WHEN TARGET NOT MATCHED THEN
INSERT VALUES(customer_name, policy_type, deductable, new_claims)
OUTPUT $action, INSERTED.customer_id,
INSERTED.customer_name as [New Name],
INSERTED.policy_type as [New Policy],
INSERTED.deductable as [New Deductable],
INSERTED.number_of_claims as [New Number Of Claims],
DELETED.customer_name as [Original Name],
DELETED.policy_type as [Original Policy],
DELETED.deductable as [Original Deductable],
DELETED.number_of_claims as [Original Number Of Claims];
GO
Sedan var det den nya möjligheten att skapa en tabell-datatyp (Table-value type) att använda som inparameter till en lagrad procedur eller funktion. Jag hade förberett genom att lägga till en Inventory-tabell i min AdventureWorks-testdatabas och fyllt den med testdata, så jag har lagt till de stegen till mitt demo script:
USE AdventureWorks
-- Clean up
IF EXISTS (SELECT * FROM sys.procedures sp JOIN sys.schemas ss ON sp.schema_id = ss.schema_id WHERE sp.name = N'myProc' AND ss.name = N'dbo')
DROP PROCEDURE [dbo].[myProc]
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'myTableType' AND ss.name = N'dbo')
DROP TYPE [dbo].[myTableType]
CREATE TABLE Inventory (id INT,
name NVARCHAR(100), qty INT);
GO
INSERT INTO Inventory VALUES (1, 'Bicycle', 2),
(2, 'Roller blades', 5), (3, 'Soccer ball', 10);
GO
SELECT id, name, qty FROM Inventory
-- Create a user TABLE type
CREATE TYPE myTableType AS TABLE (id INT,
name NVARCHAR(100), qty INT);
GO
-- Create a stored procedure that accepts a table-variable
-- of type TABLE as a parameter
CREATE PROCEDURE myProc (@tvp myTableType READONLY)
AS
UPDATE Inventory SET qty += tvp.qty
FROM Inventory AS i INNER JOIN @tvp AS tvp
ON i.id = tvp.id
GO
-- Declare & populate variable of the TABLE type
DECLARE @list AS myTableType
INSERT INTO @list VALUES (1, 'Bicycle', 10),
(2, 'Roller blades', 5), (3, 'Soccer ball', 25);
-- Execute the stored procedure with TVP
EXEC myProc @list;
GO
SELECT id, name, qty FROM Inventory