SQL SMARTIES TIP13 T-SQL SEXY FEATURE The mythic UPSERT Or How to use MERGE

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.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s