INSERT (Transact-SQL)
Ajoute une ou plusieurs nouvelles lignes à une table ou une vue. Pour obtenir des exemples, consultez Exemples INSERT (Transact-SQL).
.gif "Icône Lien de rubrique")
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
Vous pouvez utiliser INSERT INTO <target_table> SELECT <columns> FROM <source_table> pour transférer efficacement un grand nombre de lignes d'une table, par exemple une table intermédiaire, vers une autre table avec une journalisation minimale. La journalisation minimale peut améliorer les performances de l'instruction et réduire le risque de voir l'opération remplir l'espace disponible du journal des transactions au cours de la transaction.
La journalisation minimale pour cette instruction comporte les impératifs suivants :
Le mode de récupération de la base de données doit correspondre au mode simple ou au mode de récupération utilisant les journaux de transactions.
La table cible est un segment de mémoire vide ou non vide.
La table cible n'est pas utilisée dans la réplication.
L'indicateur TABLOCK est spécifié pour la table cible.
Les lignes insérées dans un segment de mémoire à la suite d'une action d'insertion dans une instruction MERGE peuvent également être journalisées de façon minimale.
Contrairement à l'instruction BULK INSERT, qui maintient un verrou de mise à jour en bloc moins restrictif, INSERT INTO…SELECT avec l'indicateur TABLOCK maintient un verrou exclusif (X) sur la table. Cela signifie que vous ne pouvez pas insérer de lignes à l'aide d'opérations d'insertion parallèles. Pour plus d'informations sur les verrous, consultez Modes de verrouillage.
Utilisation de OPENROWSET et de BULK pour charger en masse les données
La fonction OPENROWSET peut accepter les indicateurs de table suivants, lesquels offrent des optimisations de chargement en masse avec l'instruction INSERT :
L'indicateur TABLOCK peut réduire le nombre d'enregistrements de journal pour l'opération d'insertion. Le mode de récupération de la base de données doit être le mode simple ou le mode de récupération utilisant les journaux de transactions ; par ailleurs, la table cible ne peut pas être utilisée dans la réplication. Pour plus d'informations, consultez Conditions requises pour une journalisation minimale dans l'importation en bloc.
L'indicateur IGNORE_CONSTRAINTS peut désactiver temporairement la vérification des contraintes FOREIGN KEY et CHECK.
L'indicateur IGNORE_TRIGGERS peut temporairement désactiver l'exécution des déclencheurs.
L'indicateur KEEPDEFAULTS permet l'insertion la valeur par défaut éventuelle d'une colonne de table, à la place de la valeur NULL, lorsqu'il manque une valeur pour la colonne dans l'enregistrement de données.
L'indicateur KEEPIDENTITY permet que les valeurs d'identité figurant dans le fichier de données importé soient utilisées pour la colonne d'identité dans la table cible.
Ces optimisations sont similaires à celles disponibles avec la commande BULK INSERT. Pour plus d'informations, consultez Indicateurs de table (Transact-SQL).
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
Vous pouvez insérer des valeurs dans des colonnes de type défini par l'utilisateur en procédant comme suit :
En fournissant une valeur du type défini par l'utilisateur.
En fournissant une valeur dans un type de données système SQL Server, à 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 implicitement à 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() pour créer une 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) );
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.
Important
L'utilisation de SET ROWCOUNT n'affectera en rien les instructions DELETE, INSERT et UPDATE dans la prochaine version de SQL Server. N'utilisez pas SET ROWCOUNT avec les instructions DELETE, INSERT et UPDATE dans les nouveaux travaux de développement, et pensez à modifier les applications qui l'utilisent actuellement. Il est conseillé d'utiliser la clause TOP à la place.
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.