次の方法で共有

TSQL-exempel från Lansering i din ort

  • [アーティクル]

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

Comments