INSERT (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 ] ) [ ,...n ] 
    | derived_table 
    | execute_statement
    | <dml_table_source>
    | DEFAULT VALUES 
    } 
} 
[; ]

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

<dml_table_source> ::=
    SELECT <select_list>
    FROM ( <dml_statement_with_output_clause> ) 
      [AS] table_alias [ ( column_alias [ ,...n ] ) ]
    [ WHERE <search_condition> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]

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 communes 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 et 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 lié sur lequel se trouve la table ou la vue. server_name peut être spécifié en tant que nom de serveur lié ou à l'aide de la fonction OPENDATASOURCE.

  Lorsque server_name est spécifié en tant que serveur lié, database_name et schema_name sont obligatoires. Lorsque server_name est spécifié avec OPENDATASOURCE, database_name et schema_name peuvent ne pas s'appliquer à toutes les sources de données ; par ailleurs, ils dépendent des fonctionnalités du fournisseur OLE DB qui accède à l'objet distant. Pour plus d'informations, consultez Requêtes distribuées.

• 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 référence exactement à une table de base dans sa clause FROM. Par exemple, une instruction INSERT dans une vue contenant plusieurs tables doit utiliser un 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. L'utilisation de ces fonctions dépend des fonctionnalités du fournisseur OLE DB qui accède à l'objet distant. Pour plus d'informations, consultez Requêtes distribuées.

• 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 Indicateurs de table (Transact-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 doit pouvoir fournir une valeur basée sur la définition de la colonne ; sinon, il n'est pas possible de charger la ligne. Le moteur de base de données fournit automatiquement une valeur pour la colonne si :

  • elle possède une propriété IDENTITY. La valeur d'identité incrémentielle suivante est utilisée ;

  • elle a une valeur par défaut, La valeur par défaut de la colonne est utilisée ;

  • elle a un type de données timestamp, La valeur d'horodateur actuelle est utilisée ;

  • accepte la valeur Null. La valeur Null est utilisée ;

  • est une colonne calculée. La valeur calculée est utilisée.

  column_list et une liste de valeurs doivent être utilisés lors de l'insertion de valeurs explicites dans une colonne d'identité ; par ailleurs, 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. Les résultats peuvent être retournés à l'application de traitement ou être insérés dans une table ou une variable de table pour un traitement ultérieur.

  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 à des instructions INSERT contenant un execute_statement. La clause OUTPUT INTO n'est pas prise en charge dans les instructions INSERT qui contiennent une clause <dml_table_source>.

• VALUES
  Présente la ou les listes de valeurs de données à insérer. Il doit y avoir une valeur de donnée pour chaque colonne de column_list (le cas échéant) ou de la table. La liste de valeurs doit être mise entre parenthèses.

  Si les valeurs de la liste de valeurs 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.

  SQL Server 2008 introduit le constructeur ROW Transact-SQL (également appelé constructeur de valeurs de table) pour spécifier plusieurs lignes dans une seule instruction INSERT. Le constructeur ROW est constitué d'une seule clause VALUES comportant plusieurs listes de valeurs placées entre parenthèses et séparées par une virgule. Pour plus d'informations, consultez Constructeur de valeurs de table (Transact-SQL).

• 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 si 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'horodateur 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 EXECUTE.

  Lorsque vous faites référence aux types de données caractères Unicode nchar, nvarchar et ntext, expression doit comporter la majuscule « N » en préfixe. Si vous ne spécifiez pas « N », SQL Server convertit la chaîne dans la page de codes qui correspond au classement par défaut de la base de données ou de la colonne. Tous les caractères absents de cette page de codes sont perdus. Pour plus d'informations, consultez Programmation côté serveur avec Unicode.

• 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.

  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. Dans une transaction distribuée, l'instruction execute_statement ne peut pas être émise sur un serveur lié en boucle lorsque MARS (Multiple Active Result Set) est activé pour la connexion.

  Si execute_statement retourne des données avec l'instruction READTEXT, chaque instruction READTEXT peut retourner au maximum 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.

  Vous ne pouvez pas spécifier de paramètre table en tant que cible d'une instruction INSERT EXEC ; néanmoins, vous pouvez le spécifier en tant que source de la chaîne ou procédure stockée INSERT EXEC. Pour plus d'informations, consultez Paramètres table (Moteur de base de données).

• <dml_table_source>
  Spécifie que les lignes insérées dans la table cible sont les lignes retournées par la clause OUTPUT d'une instruction INSERT, UPDATE, DELETE ou MERGE, éventuellement filtrées par une clause WHERE. Si <dml_table_source> est spécifié, la cible de l'instruction INSERT externe doit satisfaire les restrictions ci-dessous :

  • La table doit être une table de base, et non une vue.

  • La table ne peut pas être une table distante.

  • Aucun déclencheur ne peut être défini sur la table.

  • Elle ne peut participer à aucune relation clé primaire-clé étrangère.

  • Elle ne peut pas participer à la réplication de fusion ou à des abonnements pouvant être mis à jour pour la réplication transactionnelle.

  Le niveau de compatibilité défini pour la base de données doit être 100.

• <select_list>
  Liste séparée par des virgules qui spécifie les colonnes retournées par la clause OUTPUT qu'il convient d'insérer. Les colonnes dans <select_list> doivent être compatibles avec les colonnes dans lesquelles les valeurs sont insérées. <select_list> ne peut pas faire référence à des fonctions d'agrégation ni à TEXTPTR.

  [!REMARQUE]

  Toutes les variables répertoriées dans la liste SELECT font référence à leurs valeurs d'origine, indépendamment des modifications qui leur ont été apportées dans <dml_statement_with_output_clause>.

• <dml_statement_with_output_clause>
  Instruction INSERT, UPDATE, DELETE ou MERGE valide qui retourne les lignes affectées dans une clause OUTPUT. L'instruction ne peut pas contenir de clause WITH et ne peut pas cibler les tables distantes ni les vues partitionnées. Si l'instruction UPDATE ou DELETE est spécifiée, elle ne peut pas être basée sur un curseur. Les lignes sources ne peuvent pas être référencées comme des instructions DML imbriquées.

• WHERE <search_condition>
  Toute clause WHERE contenant une condition de recherche <search_condition> valide qui filtre les lignes retournées par <dml_statement_with_output_clause>. Pour plus d'informations, consultez Condition de recherche (Transact-SQL). Utilisée dans ce contexte, la condition <search_condition> ne peut pas contenir de sous-requêtes, de fonctions scalaires définies par l'utilisateur qui permettent d'accéder aux données, de fonctions d'agrégation, TEXTPTR ni de prédicats de recherche en texte intégral.

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

Meilleures pratiques pour le chargement en masse des données

Utilisation de INSERT INTO…SELECT pour charger en masse les données avec une journalisation minimale

Utilisation de OPENROWSET et de BULK pour charger en masse les données

Types de données

Lorsque vous insérez des lignes, prenez en compte le comportement des types de données suivant :

• Si une valeur est chargée dans des colonnes de type de données char, varchar ou varbinary, le remplissage ou la troncation des espaces vides 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 à l'aide d'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 dont le type de données est varchar ou text, l'opération par défaut consiste à charger une chaîne de longueur zéro.

• 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 lui préalloue 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.

• Les colonnes créées à l'aide du type de données uniqueidentifier stockent les valeurs binaires au format 16 octets. Contrairement aux colonnes d'identité, le moteur de base de données ne génère pas automatiquement de 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îne 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).

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

Gestion des erreurs

Vous pouvez implémenter la gestion des erreurs pour l'instruction INSERT en spécifiant cette dernière dans une construction TRY…CATCH. Pour plus d'informations, consultez Utilisation de TRY...CATCH dans Transact-SQL.

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 un message d'erreur est retourné.

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 et aucune ligne n'est chargé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 avait la valeur ON. Le lot est arrêté et un message d'erreur est retourné. Si, au cours de l'évaluation de l'expression, une instruction INSERT, DELETE ou UPDATE rencontre une erreur arithmétique, un dépassement de capacité, une division par zéro ou une erreur de domaine alors que les options SET ARITHABORT et SET ANSI_WARNINGS ont la valeur OFF, SQL Server insère ou met à jour une valeur NULL. Si la colonne cible ne peut pas prendre la valeur Null, l'action d'insertion ou de mise à jour échoue et l'utilisateur reçoit une erreur. Pour plus d'informations, consultez Conséquences si ARITHABORT et ARITHIGNORE ont pour valeur ON.

Interopérabilité

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. Pour plus d'informations sur les déclencheurs INSTEAD OF, consultez CREATE TRIGGER (Transact-SQL).

Limitations et restrictions

Lorsque vous insérez des valeurs dans des tables distantes et que toutes les valeurs des colonnes ne sont pas spécifiées, vous devez identifier les colonnes dans lesquelles les valeurs spécifiées doivent être insérées.

La valeur de l'option SET ROWCOUNT est ignorée pour les instructions INSERT portant sur les vues partitionnées locales et distantes. Par ailleurs, cette option n'est pas prise en charge pour les instructions INSERT exécutées sur des tables distantes.

Comportement de journalisation

L'instruction INSERT est toujours entièrement journalisée, sauf lors de l'utilisation de la fonction OPENROWSET avec le mot clé BULK ou lors de l'utilisation de INSERT INTO <target_table> SELECT <columns> FROM <source_table>. Ces opérations peuvent faire l'objet d'une journalisation minimale. Pour plus d'informations, consultez la section « Meilleures pratiques pour le chargement en masse des données », précédemment dans cette rubrique.

Sécurité

Au cours d'une connexion à un serveur lié, le serveur émetteur fournit un nom et un mot de passe de connexion afin de se connecter au serveur récepteur. Pour que cette connexion fonctionne, vous devez créer un mappage de connexion entre les serveurs liés en utilisant sp_addlinkedsrvlogin. Pour plus d'informations, consultez Sécurité des serveurs liés.

Lorsque vous utilisez OPENROWSET(BULK…), il est important que vous compreniez la manière dont SQL Server gère l'emprunt d'identité. Pour plus d'informations, consultez « Considérations sur la sécurité » dans Importation de données en bloc à l'aide de BULK INSERT ou OPENROWSET(BULK...).

Autorisations

L'autorisation INSERT est obligatoire sur la table cible.

Les autorisations INSERT sont attribuées par défaut aux membres du rôle 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 des rôles sysadmin, db_owner et db_securityadmin, ainsi que le propriétaire de la table peuvent transférer des autorisations à d'autres utilisateurs.

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

Exemples

Pour obtenir des exemples, consultez Exemples INSERT (Transact-SQL).

Voir aussi

Référence