[SQL SERVER 2008] Utilisation de la commande transact SQL MERGE
L’idée est la suivante : j’ai une table stock qui contient des produits et leur quantité, par ailleurs j’ai une table livraison qui contient des produits et leur quantité ce qui correspond au contenu du camion (oui Robert il est beau ton camion).
Quand il y a des produits dans le camion de Robert qui existe déjà dans mon stock, je veux que les quantités s’ajoutent. Quand il y a des produits en livraison que je n’ai pas en stock je veux créer une nouvelle ligne dans ma table stock.
Voici le script de création des deux tables :
USE [Base_stock]
GO
CREATE TABLE [dbo].[Table_Livraison](
[Id_Livraison] [int] IDENTITY(1,1) NOT NULL,
[id_Produit_Livraison] [int] NULL,
[Produit_Livraison] [varchar](50) NULL,
[quantite_Livraison] [int] NULL,
CONSTRAINT [PK_Table_Livraison] PRIMARY KEY CLUSTERED
(
[Id_Livraison] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table_Stock](
[Id_Stock] [int] IDENTITY(1,1) NOT NULL,
[Id_Produit_Stock] [int] NULL,
[Produit_Stock] [varchar](50) NULL,
[Quantite_Stock] [int] NULL,
CONSTRAINT [PK_Table_Stock] PRIMARY KEY CLUSTERED
(
[Id_Stock] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Ensuite je vais mettre quelques données dans mes deux tables pour la démo.
use Base_stock
GO
INSERT INTO [Base_stock].[dbo].[Table_Stock]
([Id_Produit_Stock],[Produit_Stock],[Quantite_Stock])
VALUES
(1,'nutella',10),
(5,'compote',20),
(6,'brocolis',50),
(7,'pommes',30),
(8,'coca',40)
GO
INSERT INTO [Base_stock].[dbo].[Table_Livraison]
([id_Produit_Livraison],[Produit_Livraison],[quantite_Livraison])
VALUES
(2,'biere',10),
(3,'tomates',200),
(5,'compote',10),
(1,'nutella',30),
(9,'beurre',10)
GO
A noter qu’ici j’utilise une nouvelle fonctionnalité de SQL 2008 à savoir que lors d’instruction insert sur la même table je n’ai plus besoin de répéter « insert into » il suffit de séparer les entrées par des virgules.
Donc pour récapituler voici le contenu de mes 2 tables.
Bien, maintenant la mise en place du MERGE voici le script après on en parle
MERGE into dbo.Table_Stock stock
using (
select id_Produit_Livraison,Produit_Livraison,quantite_Livraison from dbo.Table_Livraison )livraison
ON (stock.Id_Produit_Stock=livraison.id_Produit_Livraison)
when matched then
UPDATE set stock.Quantite_Stock = stock.Quantite_Stock + livraison.quantite_Livraison
when not matched then
insert (Id_Produit_Stock,Produit_Stock ,Quantite_Stock)
values (id_Produit_Livraison,Produit_Livraison,quantite_Livraison);
Bon déjà il faut savoir que le MERGE ne supporte les identificateurs en plusieurs partie autrement dit je si obligé de donner un alias à mes tables pour pouvoir m’en servir ce qui donne la table dbo.Table_Stock devient stock et dbo.Table_Livraison devient livraison.
Ensuite j’écris
“
MERGE into dbo.Table_Stock stock” ce qui veut dire que ce sera la table stock qui sera mise à jour.
Pour mon travail j’ai besoin d’une source de données qui est pour moi la table livraison c’est ce qui est dit dans :
« using (
select id_Produit_Livraison,Produit_Livraison,quantite_Livraison from dbo.Table_Livraison )livraison
».
Il faut avoir un lien entre la table source et la table cible pour décider si je fais une mise à jour ou une insertion c’est la ligne suivante :
ON (stock.Id_Produit_Stock=livraison.id_Produit_Livraison)
Ensuite je vais lui dire si il y a correspondance alors tu fais une mise à jour de la quantité en ajoutant à la quantité en stock la quantité de produit livré. S’il n’y a pas de correspondance tu insère le produit livré avec la quantité livrée dans la table stock. C’est la ligne suivante :
when matched then
UPDATE set stock.Quantite_Stock = stock.Quantite_Stock + livraison.quantite_Livraison
when not matched then
insert (Id_Produit_Stock,Produit_Stock ,Quantite_Stock)
values (id_Produit_Livraison,Produit_Livraison,quantite_Livraison)
Et je finis par un point virgule nécessaire à la commande MERGE.
Le résultat est le suivant :
On voit bien que la quantité de « nutella » par exemple est passée de 10 à 40 (30 au départ + 10 livrés).
La bière qui n’était pas en stock au départ est venue s’insérer avec la quantité livrée. Etc.…
Voilà une commande qui peut venir s’insérer facilement dans des procédures stockées, des scripts et pourquoi pas des fonctions.