Aujourd’hui
Je suis frappée de voir à quel point les gens utilisent encore peu cette instruction dans leur pratique quotidienne. J’ai constaté que même si les gens passent d’une version à l’autre de SQL la pratique du T-SQL reste très minimaliste. Dans mes cartons j’ai un tableau avec par version les nouveautés qui DEVRAIENT être adoptées… Bon yapuka…
Autre note: ce tip a été “mailjunké” chez pas mal de cleints car j’avais utilisé le mot sexy …Pfffffffffffffffffffffffffffffffffff
SQL SMARTIES TIP13
T-SQL SEXY FEATURE: The mythic UPSERT Or How to use MERGE
One of the most banal problem we have to resolve is the synchronization between tables and records.
And then we have the classical question :
Insert or Update? (the UPSERT well know pseudo code)
Does already exists my record in this table ?
So we test the existence and then we choose to make one of the both statement… Typically, this is done by executing a stored procedure or batch that contains individual INSERT, UPDATE, and DELETE statements. However, this means that the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
USE AdventureWorks;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure
@UnitMeasureCode nchar(3),
@Name nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
— Update the row if it exists.
UPDATE Production.UnitMeasure
SET Name = @Name
WHERE UnitMeasureCode = @UnitMeasureCode
— Insert the row if the UPDATE statement failed.
IF (@@ROWCOUNT = 0 )
BEGIN
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
VALUES (@UnitMeasureCode, @Name)
END
END;
GO
— Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = ‘ABC’, @Name = ‘Test Value’;
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure
WHERE UnitMeasureCode = ‘ABC’;
GO
By using the MERGE statement, you can replace the individual DML statements with a single statement. This can improve query performance because the operations are performed within a single statement, therefore, minimizing the number of times the data in the source and target tables are processed.However, performance gains depend on having correct indexes, joins, and other considerations in place.
— perform the same operations using the MERGE statement.
ALTER PROCEDURE dbo.InsertUnitMeasure
@UnitMeasureCode nchar(3),
@Name nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
MERGE Production.UnitMeasure AS target
USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
ON (target.UnitMeasureCode = source.UnitMeasureCode)—Join predicate
WHEN MATCHED THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (UnitMeasureCode, Name)
VALUES (source.UnitMeasureCode, source.Name)
OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
GO
— Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = ‘ABC’, @Name = ‘New Test Value’;
EXEC InsertUnitMeasure @UnitMeasureCode = ‘XYZ’, @Name = ‘Test Value’;
EXEC InsertUnitMeasure @UnitMeasureCode = ‘ABC’, @Name = ‘Another Test Value’;
The source could be: variables, derived table, table , queries, Table Value Constructor (Transact-SQL)*
and so on…
We can also perfom a merge on this way
MERGE dbo.Fact_Generation as target
USING (SELECT day,quarter,generated_volume,generated_type FROM dbo.staging_generation) as source
ON (target.day = source.day AND target.quarter= source.quarter AND
target.generated_type_id=source.generated_type)
WHEN MATCHED THEN
UPDATE SET target.generated_volume = source.generated_volume
WHEN NOT MATCHED BY TARGET THEN
INSERT (day,quarter,generated_volume,generated_type_id )
VALUES (source.day,source.quarter,source.generated_volume,source.generated_type ) ;
For Massive Import Operation:
The MERGE statement can be used to efficiently bulk load data from a source data file (txt) into a target table by specifying the OPENROWSET(BULK…) clause as the table source. By doing so, the entire file is processed in a single batch.
On a next tip I will discuss over the right choice between SSIS to import and synchronize data.
The whole syntax and examples in the BOL
Additionnal resources : http://msdn.microsoft.com/en-us/library/cc879317.aspx
*Table values constructor is a new T-SQL feature I will discuss in another tip.
DatAtypical 