• Article

Exemples INSERT (Transact-SQL)

Cette rubrique fournit des exemples d'utilisation de l'instruction Transact-SQL INSERT dans SQL Server 2008 R2. Les exemples INSERT sont regroupés à l'aide des catégories suivantes.

Catégorie

Éléments syntaxiques proposés

Syntaxe de base

INSERT • constructeur de valeurs de table

Gestion de valeurs de colonnes

IDENTITY • NEWID • valeurs par défaut • types définis par l'utilisateur

Insertion de données à partir d'autres tables

INSERT…SELECT • INSERT…EXECUTE • WITH expression de table commune • TOP

Spécification d'objets cibles autres que les tables standard

Vues • variables de table

Insertion de lignes dans une table distante

Serveur lié • fonction d'ensemble de lignes OPENQUERY • fonction d'ensemble de lignes OPENDATASOURCE

Importation en masse de données à partir de tables ou de fichiers de données

INSERT…SELECT • fonction OPENROWSET

Substitution du comportement par défaut de l'optimiseur de requête à l'aide d'indicateurs

Indicateurs de table

Capture des résultats de l'instruction INSERT

Clause OUTPUT

Syntaxe de base

Les exemples fournis dans cette section présentent les fonctionnalités de base de l'instruction INSERT en utilisant la syntaxe minimale requise.

A. Insertion d'une seule ligne de données

L'exemple suivant insère une ligne dans la table Production.UnitMeasure. Les colonnes de cette table sont UnitMeasureCode, Name et ModifiedDate. Étant donné que les valeurs de toutes les colonnes sont fournies et qu'elles sont répertoriées dans le même ordre que les colonnes de la table, il n'est pas nécessaire de spécifier les noms de colonnes dans la liste de colonnes.

USE AdventureWorks2008R2;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');
GO

B. Insertion de plusieurs lignes de données

L'exemple suivant utilise le constructeur de valeurs de table pour insérer trois lignes dans la table Production.UnitMeasure en une seule instruction INSERT. Étant donné que les valeurs de toutes les colonnes sont fournies et qu'elles sont répertoriées dans le même ordre que les colonnes de la table, il n'est pas nécessaire de spécifier les noms de colonnes dans la liste de colonnes.

USE AdventureWorks2008R2;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');
GO

C. Insertion de données qui ne sont pas dans le même ordre que les colonnes de la table

L'exemple suivant utilise une liste de colonnes afin de spécifier de manière explicite les valeurs insérées dans chaque colonne. L'ordre des colonnes de la table Production.UnitMeasure est UnitMeasureCode, Name, ModifiedDate ; cependant, les colonnes ne sont pas répertoriées dans cet ordre dans column_list..

USE AdventureWorks2008R2;
GO
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,
    ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());
GO

Gestion de valeurs de colonnes

Les exemples fournis dans cette section présentent des méthodes d'insertion de valeurs dans des colonnes qui sont définies avec une propriété IDENTITY, une valeur DEFAULT, des types de données tels que uniqueidentifer ou des colonnes d'un type défini par l'utilisateur.

A. Insertion de données dans une table dont les colonnes ont des valeurs par défaut

L'exemple suivant illustre l'insertion de lignes dans une table dont les colonnes génèrent automatiquement une valeur ou possèdent une valeur par défaut. Column_1 est une colonne calculée qui génère automatiquement une valeur en concaténant une chaîne avec la valeur insérée dans column_2. Column_2 est définie avec une contrainte par défaut. Si aucune valeur n'est spécifiée pour cette colonne, la valeur par défaut est utilisée. Column_3 est défini avec le type de données rowversion, lequel génère automatiquement un nombre binaire incrémentiel unique. Column_4 ne génère pas automatiquement de valeurs. Lorsqu'aucune valeur n'est spécifiée pour cette colonne, NULL est inséré. Les instructions INSERT insèrent des lignes qui contiennent des valeurs pour certaines colonnes, mais pas pour toutes. Dans la dernière instruction INSERT, aucune colonne n'est spécifiée et seules les valeurs par défaut sont insérées à l'aide de la clause DEFAULT VALUES.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 AS 'Computed column ' + column_2, 
    column_2 varchar(30) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 rowversion,
    column_4 varchar(40) NULL
);
GO
INSERT INTO dbo.T1 (column_4) 
    VALUES ('Explicit value');
INSERT INTO dbo.T1 (column_2, column_4) 
    VALUES ('Explicit value', 'Explicit value');
INSERT INTO dbo.T1 (column_2) 
    VALUES ('Explicit value');
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2, column_3, column_4
FROM dbo.T1;
GO

B. Insertion de données dans une table qui comprend une colonne d'identité

L'exemple suivant illustre différentes méthodes d'insertion de données dans une colonne d'identité. Les deux premières instructions INSERT permettent la génération de valeurs d'identité pour les nouvelles lignes. La troisième instruction INSERT substitue la propriété IDENTITY de la colonne à l'aide de l'instruction SET IDENTITY_INSERT et insère une valeur explicite dans la colonne d'identité.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));
GO
INSERT T1 VALUES ('Row #1');
INSERT T1 (column_2) VALUES ('Row #2');
GO
SET IDENTITY_INSERT T1 ON;
GO
INSERT INTO T1 (column_1,column_2) 
    VALUES (-99, 'Explicit identity value');
GO
SELECT column_1, column_2
FROM T1;
GO

C. Insertion de données dans une colonne uniqueidentifier en utilisant NEWID()

L'exemple suivant utilise la fonction NEWID() pour obtenir un identificateur global unique (GUID) pour column_2. Contrairement à ce qui se passe pour les colonnes d'identité, le moteur de base de données ne génère pas automatiquement de valeurs pour les colonnes ayant le type de données uniqueidentifier, comme l'indique la deuxième instruction INSERT.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 int IDENTITY, 
    column_2 uniqueidentifier,
);
GO
INSERT INTO dbo.T1 (column_2) 
    VALUES (NEWID());
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2
FROM dbo.T1;
GO

D. Insertion de données dans des colonnes de type défini par l'utilisateur

Les instructions Transact-SQL suivantes insèrent trois lignes dans la colonne PointValue de table Points. Cette colonne utilise un type clr défini par l'utilisateur (UDT). Le type de données Point comprend des valeurs entières X et Y qui sont exposées en tant que propriétés du type UDT. Vous devez utiliser la fonction CAST ou CONVERT pour effectuer une conversion de type (transtypage) des valeurs X et Y délimitées par des virgules en type Point. Les deux premières instructions utilisent la fonction CONVERT pour convertir une valeur de chaîne en type Point, et la troisième instruction utilise la fonction CAST. Pour plus d'informations, consultez Manipulation de données UDT.

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));

Insertion de données à partir d'autres tables

Les exemples fournis dans cette section présentent des méthodes d'insertion de lignes d'une table dans une autre table.

A. Utilisation des options SELECT et EXECUTE pour insérer des données provenant d'autres tables

L'exemple suivant montre comment insérer des données d'une table dans une autre table à l'aide de INSERT…SELECT ou de INSERT…EXECUTE. Chaque méthode s'appuie sur une instruction SELECT basée sur plusieurs tables qui inclut une expression et une valeur littérale dans la liste des colonnes.

La première instruction INSERT utilise directement une instruction SELECT pour dériver les données des tables sources (Employee, SalesPerson et Person) et stocker le jeu de résultats dans la table EmployeeSales . La deuxième instruction INSERT utilise la clause EXECUTE pour appeler une procédure stockée qui contient l'instruction SELECT, et la troisième instruction INSERT utilise la clause EXECUTE pour référencer l'instruction SELECT en tant que chaîne littérale.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
IF OBJECT_ID ('dbo.uspGetEmployeeSales', 'P') IS NOT NULL
    DROP PROCEDURE uspGetEmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( DataSource   varchar(20) NOT NULL,
  BusinessEntityID   varchar(11) NOT NULL,
  LastName     varchar(40) NOT NULL,
  SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales 
AS 
    SET NOCOUNT ON;
    SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName, 
        sp.SalesYTD 
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...SELECT example
INSERT INTO dbo.EmployeeSales
    SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD 
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT INTO dbo.EmployeeSales 
EXECUTE dbo.uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT INTO dbo.EmployeeSales 
EXECUTE 
('
SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName, 
    sp.SalesYTD 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE ''2%''
    ORDER BY sp.BusinessEntityID, c.LastName
');
GO
--Show results.
SELECT DataSource,BusinessEntityID,LastName,SalesDollars
FROM dbo.EmployeeSales;
GO

B. Utilisation de l'expression de table commune WITH pour définir les données insérées

L'exemple suivant crée la table NewEmployee. Une expression de table commune (EmployeeTemp) définit les lignes d'une ou de plusieurs tables à insérer dans la table NewEmployee. L'instruction INSERT référence les colonnes dans l'expression de table commune.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    EmployeeID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    PhoneNumber Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
WITH EmployeeTemp (EmpID, LastName, FirstName, Phone, 
                   Address, City, StateProvince, 
                   PostalCode, CurrentFlag)
AS (SELECT 
       e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN Person.BusinessEntityAddress AS bea
        ON e.BusinessEntityID = bea.BusinessEntityID
        INNER JOIN Person.Address AS a
        ON bea.AddressID = a.AddressID
        INNER JOIN Person.PersonPhone AS pp
        ON e.BusinessEntityID = pp.BusinessEntityID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Person as c
        ON e.BusinessEntityID = c.BusinessEntityID
    )
INSERT INTO HumanResources.NewEmployee 
    SELECT EmpID, LastName, FirstName, Phone, 
           Address, City, StateProvince, PostalCode, CurrentFlag
    FROM EmployeeTemp;
GO

C. Utilisation de TOP pour limiter les données insérées à partir de la table source

L'exemple suivant utilise la clause TOP pour limiter le nombre de lignes insérées dans la table NewEmployee à partir de la table Employee. Il y insère les données d'adresse du premier jeu aléatoire de 10 employés de la table Employee. L'instruction SELECT est ensuite exécutée pour vérifier le contenu de la table NewEmployee.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    BusinessEntityID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    PhoneNumber Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
-- Insert 10 random rows into the table NewEmployee.
INSERT TOP (10) INTO HumanResources.NewEmployee 
    SELECT
       e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN Person.BusinessEntityAddress AS bea
        ON e.BusinessEntityID = bea.BusinessEntityID
        INNER JOIN Person.Address AS a
        ON bea.AddressID = a.AddressID
        INNER JOIN Person.PersonPhone AS pp
        ON e.BusinessEntityID = pp.BusinessEntityID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Person as c
        ON e.BusinessEntityID = c.BusinessEntityID;
GO
SELECT  BusinessEntityID, LastName, FirstName, PhoneNumber,
        AddressLine1, City, State, PostalCode, CurrentFlag
FROM HumanResources.NewEmployee;
GO

Spécification d'objets cibles autres que les tables standard

Les exemples présentés dans cette section montrent comment insérer des lignes en spécifiant une variable de table ou de vue.

A. Insertion de données en spécifiant une vue

L'exemple suivant spécifie un nom de vue comme objet cible ; cependant, la nouvelle ligne est insérée dans la table de base sous-jacente. L'ordre des valeurs dans l'instruction INSERT doit correspondre à l'ordre des colonnes de la vue. Pour plus d'informations, consultez Modification de données par l'intermédiaire d'une vue.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
IF OBJECT_ID ('dbo.V1', 'V') IS NOT NULL
    DROP VIEW dbo.V1;
GO
CREATE TABLE T1 ( column_1 int, column_2 varchar(30));
GO
CREATE VIEW V1 AS 
SELECT column_2, column_1 
FROM T1;
GO
INSERT INTO V1 
    VALUES ('Row 1',1);
GO
SELECT column_1, column_2 
FROM T1;
GO
SELECT column_1, column_2
FROM V1;
GO

B. Insertion de données dans une variable de table

L'exemple suivant spécifie une variable de table comme objet cible.

USE AdventureWorks2008R2;
GO
-- Create the table variable.
DECLARE @MyTableVar table(
    LocationID int NOT NULL,
    CostRate smallmoney NOT NULL,
    NewCostRate AS CostRate * 1.5,
    ModifiedDate datetime);

-- Insert values into the table variable.
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)
    SELECT LocationID, CostRate, GETDATE() FROM Production.Location
    WHERE CostRate > 0;

-- View the table variable result set.
SELECT * FROM @MyTableVar;
GO

Insertion de lignes dans une table distante

Les exemples présentés dans cette section montrent comment insérer des lignes dans une table cible distantes en utilisant un serveur lié ou une fonction d'ensemble de lignes pour référencer la table distante.

A. Insertion de données dans une table distante en utilisant un serveur lié

L'exemple suivant insère des lignes dans la table distante. L'exemple commence par créer un lien vers la source de données distante en utilisant sp_addlinkedserver. Le nom du serveur lié, MyLinkServer, est ensuite spécifié dans un nom d'objet en quatre parties qui se présente sous la forme server.catalog.schema.object.

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'server_name',
    @catalog = N'AdventureWorks2008R2';
GO

USE AdventureWorks2008R2;
GO
-- Specify the remote data source in the FROM clause using a four-part name 
-- in the form linked_server.catalog.schema.object.

INSERT INTO MyLinkServer.AdventureWorks2008R2.HumanResources.Department (Name, GroupName)
VALUES (N'Public Relations', N'Executive General and Administration');
GO

B. Insertion de données dans une table distante en utilisant la fonction OPENQUERY

L'exemple suivant insère une ligne dans une table distante en spécifiant la fonction d'ensemble de lignes OPENQUERY. Le nom de serveur lié créé dans l'exemple précédent est utilisé dans cet exemple.

-- Use the OPENQUERY function to access the remote data source.

INSERT OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2008R2.HumanResources.Department')
VALUES ('Environmental Impact', 'Engineering');
GO

C. Insertion de données dans une table distante en utilisant la fonction OPENDATASOURCE

L'exemple suivant insère une ligne dans une table distante en spécifiant la fonction d'ensemble de lignes OPENDATASOURCE. Spécifiez un nom de serveur valide pour la source de données en utilisant le format server_name ou server_name\instance_name.

-- Use the OPENDATASOURCE function to specify the remote data source.
-- Specify a valid server name for Data Source using the format server_name or server_name\instance_name.

INSERT INTO OPENDATASOURCE('SQLNCLI',
    'Data Source= <server_name>; Integrated Security=SSPI')
    .AdventureWorks2008R2.HumanResources.Department (Name, GroupName)
    VALUES (N'Standards and Methods', 'Quality Assurance');
GO

Importation en masse de données à partir de tables ou de fichiers de données

Les exemples fournis dans cette section présentent deux méthodes permettant d'importer en masse (chargement en masse) des données dans une table en utilisant l'instruction INSERT.

A. Insertion de données dans un segment de mémoire avec une journalisation minimale

L'exemple suivant crée une nouvelle table (un segment de mémoire) et y insère des données provenant d'une autre table en utilisant une journalisation minimale. Il suppose que le mode de récupération de la base de données AdventureWorks2008R2 a la valeur FULL. Pour garantir une journalisation minimale, le mode de récupération de la base de données AdventureWorks2008R2 a la valeur BULK_LOGGED avant l'insertion des lignes ; il reprend ensuite la valeur FULL après l'utilisation de l'instruction INSERT INTO…SELECT. En outre, l'indicateur TABLOCK est spécifié pour la table cible Sales.SalesHistory. Cela permet de garantir que l'instruction utilise un espace minimal dans le journal des transactions et qu'elle s'exécute de manière efficace.

USE AdventureWorks2008R2;
GO
-- Create the target heap.
CREATE TABLE Sales.SalesHistory(
    SalesOrderID int NOT NULL,
    SalesOrderDetailID int NOT NULL,
    CarrierTrackingNumber nvarchar(25) NULL,
    OrderQty smallint NOT NULL,
    ProductID int NOT NULL,
    SpecialOfferID int NOT NULL,
    UnitPrice money NOT NULL,
    UnitPriceDiscount money NOT NULL,
    LineTotal money NOT NULL,
    rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
    ModifiedDate datetime NOT NULL );
GO
-- Temporarily set the recovery model to BULK_LOGGED.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY BULK_LOGGED;
GO
-- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory
INSERT INTO Sales.SalesHistory WITH (TABLOCK)
    (SalesOrderID, 
     SalesOrderDetailID,
     CarrierTrackingNumber, 
     OrderQty, 
     ProductID, 
     SpecialOfferID, 
     UnitPrice, 
     UnitPriceDiscount,
     LineTotal, 
     rowguid, 
     ModifiedDate)
SELECT * FROM Sales.SalesOrderDetail;
GO
-- Reset the recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

B. Utilisation de la fonction OPENROWSET avec BULK pour importer en masse des données dans une table

L'exemple suivant insère des lignes d'un fichier de données dans une table en spécifiant la fonction OPENROWSET. L'indicateur de table IGNORE_TRIGGERS est spécifié en vue d'optimiser les performances. Pour obtenir d'autres exemples, consultez Importation de données en bloc à l'aide de BULK INSERT ou OPENROWSET(BULK...).

-- Use the OPENROWSET function to specify the data source and specifies the IGNORE_TRIGGERS table hint.
INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)
SELECT b.Name, b.GroupName 
FROM OPENROWSET (
    BULK 'C:\SQLFiles\DepartmentData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000)AS b ;
GO

Substitution du comportement par défaut de l'optimiseur de requête à l'aide d'indicateurs

Les exemples présentés dans cette section montrent comment utiliser des indicateurs de table pour substituer temporairement le comportement par défaut de l'optimiseur de requête lors du traitement de l'instruction INSERT.

AttentionAttention

Étant donné que l'optimiseur de requête SQL Server sélectionne généralement le meilleur plan d'exécution pour une requête, nous vous recommandons de ne recourir aux indicateurs qu'en dernier ressort et seulement si vous êtes un développeur ou un administrateur de base de données expérimenté.

A. Utilisation de l'indicateur TABLOCK pour spécifier une méthode de verrouillage

L'exemple suivant spécifie qu'un verrou exclusif (X) est appliqué sur la table Production.Location et est maintenu jusqu'à la fin de l'instruction INSERT.

USE AdventureWorks2008R2;
GO
INSERT INTO Production.Location WITH (XLOCK)
(Name, CostRate, Availability)
VALUES ( N'Final Inventory', 15.00, 80.00);
GO

Capture des résultats de l'instruction INSERT

Les exemples présentés dans cette section montrent comment utiliser la clause OUTPUT pour retourner des informations de chaque ligne affectée par une instruction INSERT, ou des expressions basées sur ces lignes. Ces résultats peuvent être retournés à l'application en cours de traitement afin d'être utilisés notamment avec des messages de confirmation, des opérations d'archivage et d'autres spécifications d'application similaires.

A. Utilisation de OUTPUT avec une instruction INSERT

L'exemple suivant insère une ligne dans la table ScrapReason et utilise la clause OUTPUT pour retourner les résultats de l'instruction à la variable de table @MyTableVar. Étant donné que la colonne ScrapReasonID de la table ScrapReason est définie avec une propriété IDENTITY, aucune valeur n'est spécifiée dans l'instruction INSERT pour cette colonne. Cependant, notez que la valeur générée par le moteur de base de données pour cette colonne est retournée dans la clause OUTPUT de la colonne INSERTED.ScrapReasonID.

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

B. Utilisation de OUTPUT avec des colonnes d'identité et des colonnes calculées

L'exemple suivant crée la table EmployeeSales, puis y insère plusieurs lignes à l'aide d'une instruction INSERT, avec une instruction SELECT pour récupérer les données des tables sources. La table EmployeeSales contient une colonne d'identité (EmployeeID) et une colonne calculée (ProjectedSales). Étant donné que ces valeurs sont générées par le moteur de base de données lors de l'insertion, aucune de ces colonnes ne peut être définie dans @MyTableVar.

USE AdventureWorks2008R2 ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   int IDENTITY (1,5)NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL,
  ProjectedSales AS CurrentSales * 1.10 
);
GO
DECLARE @MyTableVar table(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
  );

INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
  OUTPUT INSERTED.LastName, 
         INSERTED.FirstName, 
         INSERTED.CurrentSales
  INTO @MyTableVar
    SELECT c.LastName, c.FirstName, sp.SalesYTD
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY c.LastName, c.FirstName;

SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

C. Insertion de données retournées à partir d'une clause OUTPUT

L'exemple suivant capture les données retournées par la clause OUTPUT d'une instruction MERGE et insère ces données dans une autre table. L'instruction MERGE met quotidiennement à jour la colonne Quantity de la table ProductInventory, en fonction des commandes traitées dans la table SalesOrderDetail. Elle supprime également les lignes correspondant aux produits dont le stock passe à 0. Cet exemple capture les lignes supprimées et les insère dans une autre table, ZeroInventory, qui effectue le suivi des produits en rupture de stock.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);
GO

INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)
SELECT ProductID, GETDATE()
FROM
(   MERGE Production.ProductInventory AS pi
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
           JOIN Sales.SalesOrderHeader AS soh
           ON sod.SalesOrderID = soh.SalesOrderID
           AND soh.OrderDate = '20070401'
           GROUP BY ProductID) AS src (ProductID, OrderQty)
    ON (pi.ProductID = src.ProductID)
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)
WHERE Action = 'DELETE';
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;