Questa mattina è stato erogato il primo webcast relativo a SQL Server 2008.
Agenda: operatore MERGE.
Di seguito il codice utilizzato per la piccola demo:
USE master
GO
/* Creazione db per tests */
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'demoMerge')
DROP DATABASE demoMerge
GO
CREATE DATABASE demoMerge
GO
USE demoMerge
GO
/* Creazione tabelle sorgente / destinazione per test */
IF OBJECT_ID('dbo.books', 'U') IS NOT NULL
DROP TABLE dbo.books
GO
IF OBJECT_ID('dbo.booksInfo', 'U') IS NOT NULL
DROP TABLE dbo.booksInfo
GO
create table books
(
idRecord int primary key identity(1,1),
description varchar(70),
code char(5) unique,
qty smallint
)
GO
create table booksInfo
(
idRecord int primary key identity(1,1),
description varchar(70),
code char(5) unique,
qty smallint
)
GO
/*
Dati per test
[books]: tabella destinazione (tabella di produzione)
[booksInfo]: tabella sorgente (tabella di alimentazione)
*/
insert books values
('Inside Microsoft SQL Server 6.5 :-)','sq650',1),
('Inside Microsoft SQL Server 2005: T-SQL Querying','sq051',21),
('Inside Microsoft SQL Server 2005: T-SQL Programming','sq052',5),
('Beginning SQL Server 2005 Programming','sq05p',12)
GO
insert booksInfo values
('Inside Microsoft SQL Server 2005: T-SQL Querying','sq051',0),
('Inside Microsoft SQL Server 2005: T-SQL Programming','sq052',1),
('Beginning SQL Server 2005 Programming','sq05p',1),
('SQL Server 2008 for developers','sq081',10),
('SQL Server 2008 for DBA','sq082', 10)
GO
/* Operatore MERGE */
MERGE
dbo.books AS [existing] -- destinazione
USING
dbo.booksInfo AS [new] -- sorgente
ON
([existing].code = [new].code)
WHEN
MATCHED AND ([existing].qty <> [new].qty)
THEN
UPDATE SET [existing].qty = [new].qty
WHEN
NOT MATCHED -- righe non presenti in destinazione
THEN
INSERT (description, code, qty)
VALUES ([new].description, [new].code, [new].qty)
WHEN
SOURCE NOT MATCHED -- righe che non esistono in sorgente
THEN
DELETE;
GO
/* Vedo i risultati */
select * from books
GO
/* Operazioni di UPSERT in Stored Procedure */
CREATE PROCEDURE up_upsertMerge
(
@code char(5),
@description VARCHAR(70), @qty smallint
)
AS
set nocount on
MERGE books t
USING (SELECT @code AS code, @description AS description,
@qty AS qty) s
ON t.code = s.code
WHEN MATCHED
AND (t.description <> s.description OR t.qty<> s.qty) THEN
UPDATE SET description = s.description, qty = s.qty
WHEN NOT MATCHED THEN
INSERT VALUES (code, description, qty);
set nocount off
go
exec dbo.up_upsertMerge @code ='sq081',
@description = 'SQL Server 2008 for developers 2nd edt',
@qty = 500
go
/* Vedo i risultati */
select * from books
GO
A breve sul sito UGISS il download di slide e demo (solution) completa!