INSERT (Transact-SQL)

Ajoute une nouvelle ligne à une table ou une vue.

Conventions de syntaxe Transact-SQL

Syntaxe

[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO] 
    { <object> | rowset_function_limited 
      [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
    }
{
    [ ( column_list ) ] 
    [ <OUTPUT Clause> ]
    { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) 
    | derived_table 
    | execute_statement 
    } 
} 
    | DEFAULT VALUES 
[; ]

<object> ::=
{ 
    [ server_name . database_name . schema_name . 
      | database_name .[ schema_name ] . 
      | schema_name . 
    ]
        table_or_view_name
}

Arguments

• WITH <common_table_expression>
  Spécifie le jeu de résultats nommé temporaire, également appelé expression de table commune, défini dans l'étendue de l'instruction INSERT. Le jeu de résultats est dérivé d'une instruction SELECT.

  Les expressions de table commune peuvent également être utilisées avec les instructions SELECT, DELETE, UPDATE et CREATE VIEW. Pour plus d'informations, consultez WITH common_table_expression (Transact-SQL).

• TOP (expression) [ PERCENT ]
  Spécifie le nombre ou le pourcentage de lignes aléatoires qui seront insérées. L'argument expression peut être un nombre ou un pourcentage de lignes. Les lignes référencées dans l'expression TOP qui sont utilisées avec INSERT, UPDATE ou DELETE ne sont disposées dans aucun ordre particulier.

  Les parenthèses délimitant expression dans TOP sont nécessaires dans les instructions INSERT, UPDATE et DELETE. Pour plus d'informations, consultez TOP (Transact-SQL).

• INTO
  Mot clé facultatif qui peut être inséré entre le mot clé INSERT et la table cible.

• server_name
  Nom du serveur (utilisant la fonction OPENDATASOURCE comme nom de serveur) sur lequel se trouve la table ou la vue. Si l'argument server_name n'est pas spécifié, database_name et schema_name sont obligatoires.

• database_name
  Nom de la base de données.

• schema_name
  Nom du schéma auquel appartient la table ou la vue

• table_or view_name
  Nom de la table ou de la vue qui doit recevoir les données.

  Une variable table, dans son étendue, peut être utilisée en tant que source de table dans une instruction INSERT.

  La vue, à laquelle fait référence table_or_view_name, doit pouvoir être mise à jour et faire précisément référence à une table de base dans sa clause FROM. Par exemple, une instruction INSERT dans une vue contenant plusieurs tables doit utiliser une column_list qui référence uniquement les colonnes d'une seule table de base. Pour plus d'informations sur les vues pouvant être mises à jour, consultez CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Fonction OPENQUERY ou OPENROWSET.

• WITH ( <table_hint_limited> [... n ] )
  Spécifie un ou plusieurs indicateurs de table autorisés pour une table cible. Le mot clé WITH et les parenthèses sont obligatoires.

  READPAST, NOLOCK et READUNCOMMITTED ne sont pas autorisés. Pour plus d'informations sur les indicateurs de table, consultez Indicateur de table (T-SQL).

  Important :

  La possibilité de spécifier les indicateurs HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD ou UPDLOCK sur les tables qui sont des cibles d'instructions INSERT sera supprimée dans une future version de SQL Server. Ces indicateurs n'affectent pas les performances des instructions INSERT. Évitez de les utiliser dans un nouveau travail de développement et prévoyez la modification des applications qui les utilisent actuellement.

  La spécification de l'indicateur TABLOCK sur une table qui est la cible d'une instruction INSERT a le même effet que la spécification de l'indicateur TABLOCKX. Un verrou exclusif est appliqué à la table.

• (column_list)
  Liste d'une ou de plusieurs colonnes dans lesquelles les données doivent être insérées. column_list doit être entre parenthèses et délimité par des virgules.

  Si une colonne ne se trouve pas dans column_list, le moteur de base de données SQL Server 2005 doit pouvoir fournir une valeur basée sur la définition de la colonne ; dans le cas contraire, il n'est pas possible de charger la ligne. Le moteur de base de données fournit automatiquement une valeur pour la colonne dans les cas suivants :

  • Si la colonne a une propriété IDENTITY, la valeur d'identité incrémentielle suivante est utilisée.
  • Si la colonne a une valeur par défaut, cette valeur par défaut est utilisée.
  • Si la colonne a un type de données timestamp, la valeur d'horodatage actuelle est utilisée.
  • Si la colonne accepte les valeurs NULL, une valeur NULL est utilisée.
  • Si la colonne est une colonne calculée, la valeur calculée est utilisée.

  column_list et la liste VALUES doivent être utilisées lors de l'insertion de valeurs explicites dans une colonne d'identité et l'option SET IDENTITY_INSERT doit avoir la valeur ON pour la table.

• Clause OUTPUT
  Retourne des lignes insérées dans le cadre de l'opération d'insertion. La clause OUTPUT n'est pas prise en charge dans les instructions DML qui font référence à des vues partitionnées locales, à des vues partitionnées distribuées, à des tables distantes, ou encore à des instructions INSERT contenant un execute_statement.

• VALUES
  Présente la liste des valeurs de données à insérer. Il doit y avoir une valeur de donnée pour chaque colonne de la liste column_list (si spécifiée) ou de la table. La liste des valeurs doit être entre parenthèses.

  Si les valeurs de la liste VALUES ne sont pas dans le même ordre que les colonnes de la table ou n'ont pas de valeur pour chaque colonne de la table, column_list doit être utilisé afin de spécifier de manière explicite la colonne qui stocke chaque valeur entrante.

• DEFAULT
  Force le moteur de base de données à charger la valeur par défaut définie pour une colonne. S'il n'existe pas de valeur par défaut pour la colonne et que celle-ci autorise les valeurs NULL, NULL est inséré. Pour une colonne définie à l'aide du type de données timestamp, la valeur d'horodatage suivante est insérée. DEFAULT n'est pas valide pour une colonne d'identité.

• expression
  Constante, variable ou expression. L'expression ne peut pas contenir d'instruction SELECT ou EXECUTE.

• derived_table
  Toute instruction SELECT valide qui retourne des lignes de données à charger dans la table. L'instruction SELECT ne peut pas contenir une expression de table commune.

• execute_statement
  Toute instruction EXECUTE valide qui retourne des données avec les instructions SELECT ou READTEXT. L'instruction SELECT ne peut pas contenir une expression de table commune.

  Si execute_statement est utilisé avec INSERT, chaque jeu de résultats doit être compatible avec les colonnes de la table ou de column_list.

  execute_statement peut être utilisé pour exécuter des procédures stockées sur le même serveur ou sur un serveur distant. La procédure du serveur distant est exécutée et les jeux de résultats sont retournés au serveur local où ils sont chargés dans la table.

  SQL Server 2008 change la sémantique de transaction des instructions INSERT...EXECUTE qui sont exécutées sur un serveur lié en boucle. Dans SQL Server 2005, ce scénario n'est pas pris en charge et génère une erreur. Dans SQL Server 2008, une instruction INSERT...EXECUTE peut être exécutée sur un serveur lié en boucle lorsque la fonctionnalité MARS (Multiple Active Result Set) n'est pas activée sur la connexion. Lorsque la fonctionnalité MARS est activée sur la connexion, le comportement est identique à celui de SQL Server 2005.

  Si execute_statement retourne des données avec l'instruction READTEXT, chaque instruction READTEXT peut retourner un maximum de 1 Mo (1 024 Ko) de données. execute_statement peut également être utilisé avec des procédures étendues. execute_statement insère les données retournées par le thread principal de la procédure étendue ; cependant, les sorties de threads autres que le thread principal ne sont pas insérées.

• DEFAULT VALUES
  Force la nouvelle ligne à prendre les valeurs par défaut définies pour chaque colonne.

Notes

INSERT ajoute de nouvelles lignes à une table. Pour remplacer des données dans une table, les instructions DELETE ou TRUNCATE TABLE doivent être utilisées pour supprimer les données existantes avant de charger de nouvelles données à l'aide de INSERT. Pour modifier des valeurs de colonne dans des lignes existantes, utilisez UPDATE. Pour créer une nouvelle table et y charger des données en une seule étape, utilisez l'option INTO de l'instruction SELECT.

Les colonnes créées à l'aide du type de données uniqueidentifier stockent les valeurs de type binaire au format 16 octets. Contrairement à ce qu'il se passe avec les colonnes d'identité, le moteur de base de données ne génère pas automatiquement des valeurs pour les colonnes comportant le type de données uniqueidentifier. Lors d'une opération d'insertion, les variables dont le type de données est uniqueidentifier et les constantes de chaînes au format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (36 caractères, tirets inclus, x correspondant à un chiffre hexadécimal compris entre 0 et 9 ou a et f) peuvent être utilisées pour les colonnes uniqueidentifier. Par exemple, 6F9619FF-8B86-D011-B42D-00C04FC964FF est une valeur valide pour une variable ou une colonne uniqueidentifier. Utilisez la fonction NEWID() afin d'obtenir un GUID (identificateur global unique).

La valeur de l'option SET ROWCOUNT est ignorée pour les instructions INSERT portant sur les vues partitionnées locales et distantes. En outre, cette option n'est pas prise en charge pour les instructions INSERT portant sur les tables distantes dans le moteur de base de données lorsque le niveau de compatibilité ascendante a la valeur 80 ou une valeur plus élevée.

Lorsqu'une instruction INSERT rencontre une erreur arithmétique (dépassement de capacité, division par zéro ou erreur de domaine) lors de l'évaluation de l'expression, le moteur de base de données gère ces erreurs comme si SET ARITHABORT était activée (valeur ON). Le reste du lot est annulé et un message d'erreur est retourné.

Règles d'insertion des lignes

Lorsque vous insérez des lignes, les règles suivantes sont appliquées :

• Si une valeur est chargée dans des colonnes de type char, varchar ou varbinary, le remplissage ou la troncature des espaces blancs de fin (espaces pour char et varchar, zéros pour varbinary) est déterminé par la valeur de SET ANSI_PADDING définie pour la colonne lors de la création de la table. Pour plus d'informations, consultez SET ANSI_PADDING (Transact-SQL).
  Le tableau suivant illustre l'opération par défaut pour SET ANSI_PADDING OFF.

  Type de données	Opération par défaut
  char	Remplit la valeur par des espaces jusqu'à la largeur définie pour la colonne.
  varchar	Supprime les espaces de fin jusqu'au dernier caractère différent d'un espace ou jusqu'au dernier caractère d'espacement simple pour les chaînes composées uniquement d'espaces.
  varbinary	Supprime les zéros à droite.

• Si une chaîne vide (' ') est chargée dans une colonne de type varchar ou text, l'opération par défaut consiste à charger la chaîne de longueur zéro.

• Si une instruction INSERT enfreint une contrainte ou une règle, ou si elle comprend une valeur incompatible avec le type de données de la colonne, l'instruction échoue et le moteur de base de données affiche un message d'erreur.

• L'insertion d'une valeur NULL dans une colonne de type text ou image ne crée pas un pointeur de texte valide et ne prédéfinit pas une page texte de 8 Ko. Pour plus d'informations sur l'insertion de données text et image, consultez Utilisation des fonctions text, ntext et image.

• Si INSERT charge plusieurs lignes à l'aide de SELECT ou EXECUTE, toute violation de règle ou de contrainte à partir des valeurs chargées met fin à l'instruction entière et aucune ligne n'est chargée.

• Lorsque vous insérez des valeurs dans des tables dans une instance distante du moteur de base de données, et que les valeurs de toutes les colonnes ne sont pas toutes spécifiées, vous devez identifier les colonnes dans lesquelles les valeurs spécifiées doivent être insérées.

Utilisation des déclencheurs INSTEAD OF sur des actions INSERT

Lorsqu'un déclencheur INSTEAD OF est défini sur des actions INSERT dans une table ou une vue, il est exécuté au lieu de l'instruction INSERT. Les versions précédentes de SQL Server prennent en charge les déclencheurs AFTER définis sur DELETE et les autres instructions de modification des données. Pour plus d'informations sur les déclencheurs INSTEAD OF, consultez CREATE TRIGGER (Transact-SQL).

Insertion de valeurs dans des colonnes de type défini par l'utilisateur

Vous pouvez insérez des valeurs dans des colonnes de type défini par l'utilisateur en procédant comme suit :

• En fournissant une valeur de type défini par l'utilisateur.

• En fournissant une valeur dans un type de données système SQL Server 2005, à condition que le type défini par l'utilisateur prenne en charge la conversion implicite ou explicite de ce type. L'exemple suivant montre comment insérer une valeur dans une colonne de type défini par l'utilisateur Point, en effectuant la conversion de manière explicite à partir d'une chaîne.

  INSERT INTO Cities (Location)
  VALUES ( CONVERT(Point, '12.3:46.2') );
  

  Une valeur binaire peut également être fournie sans effectuer de conversion explicite, car tous les types définis par l'utilisateur sont convertis explicitement à partir d'une valeur binaire. Pour plus d'informations sur la conversion et sur les types définis par l'utilisateur, consultez Exécution d'opérations sur des types définis par l'utilisateur.

• En appelant une fonction définie par l'utilisateur qui retourne une valeur du type défini par l'utilisateur. L'exemple suivant utilise une fonction définie par l'utilisateur CreateNewPoint() afin de créer une nouvelle valeur de type défini par l'utilisateur Point et insérer la valeur dans la table Cities.

  INSERT INTO Cities (Location)
  VALUES ( dbo.CreateNewPoint(x, y) );
  

Utilisation de OPENROWSET et de BULK pour le chargement en bloc

Dans le moteur de base de données SQL Server 2005, de nouveaux indicateurs de table disponibles avec le fournisseur d'ensembles de lignes en bloc OPENROWSET offrent les optimisations de chargement en bloc ci-dessous avec l'instruction INSERT :

• L'enregistrement du chargement en bloc (minimisation du nombre d'enregistrements du journal pour l'opération d'insertion)
• La vérification de contrainte peut avoir la valeur ON ou OFF
• L'exécution de déclencheur peut avoir la valeur ON ou OFF

Ces optimisations sont similaires à celles disponibles avec la commande BULK INSERT.

Lorsqu'une instruction INSERT effectue un chargement en bloc dans une table nonempty, les améliorations des performances sont les suivantes :

• Lorsqu'une page est fractionnée au cours du chargement en bloc, il n'est pas nécessaire que les nouvelles lignes ajoutées à la page soient entièrement enregistrées.
• S'il y a des index non-cluster, mais aucun index cluster sur la table, il peut être nécessaire d'enregistrer intégralement chaque ligne d'index, mais pas les lignes de données.

Pour plus d'informations, consultez OPENROWSET (Transact-SQL) et Indicateur de table (T-SQL).

Autorisations

L'autorisation INSERT est obligatoire sur la table cible.

Les autorisations INSERT sont octroyées par défaut aux membres du rôle de serveur fixe sysadmin, aux membres des rôles de base de données fixes db_owner et db_datawriter et au propriétaire de la table. Les membres appartenant aux rôles sysadmin, db_owner et db_securityadmin, ainsi que le propriétaire de la table peuvent attribuer les autorisations à d'autres utilisateurs.

Pour exécuter INSERT avec l'option BULK de la fonction OPENROWSET, vous devez être membre du rôle de serveur fixe sysadmin ou du rôle de serveur fixe bulkadmin.

Exemples

A. Exécution d'une instruction INSERT simple

Cet exemple insère un ligne dans la table Production.UnitMeasure. É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 colonne dans column_list..

USE AdventureWorks;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'F2', N'Square Feet', GETDATE());
GO

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

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

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

C. Insertion de données avec un nombre de valeurs inférieur à celui des colonnes

L'exemple suivant montre l'insertion de lignes dans une table dont les colonnes génèrent automatiquement une valeur ou possèdent une valeur par défaut. 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.

USE AdventureWorks;
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) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 timestamp,
    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

D. 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 acceptent la génération de valeurs d'identité pour les nouvelles lignes. La troisième instruction INSERT remplace 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 AdventureWorks;
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

E. 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 aux 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 AdventureWorks;
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

E. Insertion de données dans une table via une vue

L'exemple suivant spécifie un nom de vue dans l'instruction INSERT ; cependant, la nouvelle ligne est insérée dans la table sous-jacente de la vue. L'ordre de la liste VALUES dans l'instruction INSERT doit correspondre à l'ordre des colonnes de la vue.

USE AdventureWorks;
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

G. Insertion de données à l'aide des options SELECT et EXECUTE

Cet exemple illustre trois méthodes d'obtention des données d'une table et leur insertion dans une autre. Chaque méthode est basée sur une instruction SELECT multitable incluant une expression et une valeur littérale dans la liste des colonnes.

La première instruction INSERT utilise directement une instruction SELECT pour extraire des données des tables source (Employee, SalesPerson et Contact) et stocker le jeu de résultats dans la table EmployeeSales . La deuxième instruction INSERT exécute une procédure qui comprend l'instruction SELECT, et la troisième instruction INSERT exécute l'instruction SELECT comme une chaîne littérale.

USE AdventureWorks;
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,
  EmployeeID   varchar(11) NOT NULL,
  LastName     varchar(40) NOT NULL,
  SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales 
AS 
    SET NOCOUNT ON;
    SELECT 'PROCEDURE', e.EmployeeID, c.LastName, 
        sp.SalesYTD 
    FROM HumanResources.Employee AS e 
        INNER JOIN Sales.SalesPerson AS sp  
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...SELECT example
INSERT dbo.EmployeeSales
    SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD 
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT EmployeeSales 
EXECUTE uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT EmployeeSales 
EXECUTE 
('
SELECT ''EXEC STRING'', e.EmployeeID, c.LastName, 
    sp.SalesYTD 
    FROM HumanResources.Employee AS e 
        INNER JOIN Sales.SalesPerson AS sp 
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE ''2%''
    ORDER BY e.EmployeeID, c.LastName
');
GO
--Show results.
SELECT DataSource,EmployeeID,LastName,SalesDollars
FROM dbo.EmployeeSales;
GO

H. Insertion de données avec la clause TOP

L'exemple suivant crée la table NewEmployee et y insère les données d'adresse des 10 premiers employés de la table Employee. L'instruction SELECT est ensuite exécutée pour vérifier le contenu de la table NewEmployee.

USE AdventureWorks;
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,
    Phone 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 TOP (10) INTO HumanResources.NewEmployee 
    SELECT
       e.EmployeeID, c.LastName, c.FirstName, c.Phone,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN HumanResources.EmployeeAddress AS ea
        ON e.EmployeeID = ea.EmployeeID
        INNER JOIN Person.Address AS a
        ON ea.AddressID = a.AddressID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Contact as c
        ON e.ContactID = c.ContactID;
GO
SELECT  EmployeeID, LastName, FirstName, Phone,
        AddressLine1, City, State, PostalCode, CurrentFlag
FROM HumanResources.NewEmployee;
GO

I. 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 @MyTableVartable. Étant donné que la colonne ScrapReasonID est définie avec une propriété IDENTITY, il n'est pas spécifié de valeur 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 AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID 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 ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

J. Utilisation de l'expression de table commune WITH avec une instruction INSERT

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

USE AdventureWorks;
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,
    Phone 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.EmployeeID, c.LastName, c.FirstName, c.Phone,
        a.AddressLine1, a.City, sp.StateProvinceCode, 
        a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN HumanResources.EmployeeAddress AS ea
        ON e.EmployeeID = ea.EmployeeID
        INNER JOIN Person.Address AS a
        ON ea.AddressID = a.AddressID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Contact as c
        ON e.ContactID = c.ContactID
    )
INSERT INTO HumanResources.NewEmployee 
    SELECT EmpID, LastName, FirstName, Phone, 
           Address, City, StateProvince, PostalCode, CurrentFlag
    FROM EmployeeTemp;
GO

K. Utilisation de OUTPUT avec des colonnes d'identité et 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 extraire les données des tables sources. La table EmployeeSales contient une colonne d'identité (EmployeeID) et une colonne calculée (ProjectedSales). Comme 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 AdventureWorks ;
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 HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID 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

Voir aussi

Référence

BULK INSERT (Transact-SQL)
CREATE TABLE (Transact-SQL)
DELETE (Transact-SQL)
EXECUTE (Transact-SQL)
FROM (Transact-SQL)
IDENTITY (Propriété) (Transact-SQL)
NEWID (Transact-SQL)
SELECT (Transact-SQL)
SET ROWCOUNT (Transact-SQL)
UPDATE (Transact-SQL)

Autres ressources

Insertion de données dans une table

Aide et Informations

Assistance sur SQL Server 2005