Citation :
Référence de Transact-SQL
INSERT
Ajoute une nouvelle ligne à une table ou une vue.
Syntaxe
INSERT [ INTO]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
{ [ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES
< table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}
Arguments
[INTO]
Mot clé facultatif qui peut être inséré entre le mot clé INSERT et la table cible.
table_name
Nom d'une table ou d'une variable de table qui doit recevoir les données.
WITH (<table_hint_limited> [...n])
Une ou plusieurs options de table autorisées 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, voir FROM.
view_name
Nom et alias facultatif d'une vue. La vue dont la référence est view_name doit pouvoir être mise à jour. Les modifications apportées par l'instruction INSERT ne peuvent pas affecter plus d'une des tables de base référencées dans la clause FROM de la vue. Par exemple, une instruction INSERT dans une vue contenant plusieurs tables doit utiliser une column_list qui fait uniquement référence aux colonnes d'une seule table de base. Pour plus d'informations sur les vues pouvant être mises à jour, voir CREATE VIEW.
rowset_function_limited
Fonction OPENQUERY ou OPENROWSET. Pour plus d'informations, voir OPENQUERY et OPENROWSET.
(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, Microsoft® SQL Server 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. SQL Server fournit automatiquement une valeur pour la colonne si :
elle a 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 date/heure courante est utilisée ;
elle accepte les valeurs NULL, une valeur NULL 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.
VALUES
Introduit 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 SQL Server à 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 qu'elle admet les valeurs NULL, NULL sera insérée. Pour une colonne définie à l'aide du type de données timestamp, la valeur date/heure 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 renvoie des lignes de données à charger dans la table.
execute_statement
Toute instruction EXECUTE valide qui renvoie des données avec les instructions SELECT ou READTEXT.
Si vous utilisez execute_statement 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 renvoyés au serveur local où ils sont chargés dans la table. Si execute_statement renvoie des données avec l'instruction READTEXT, chaque instruction READTEXT peut renvoyer un maximum de 1 mégaoctet de données (1024 Ko). execute_statement peut également être utilisé avec les procédures étendues et insère les données renvoyées par le thread principal de cette procédure étendue. Les sorties provenant de threads autres que le thread principal ne sont pas insérées.
Remarque Pour SQL Server version 7.0, execute_statement ne peut pas contenir une procédure stockée étendue qui renvoie des colonnes de type text ou image. Il s'agit là d'une différence par rapport aux versions antérieures de SQL Server.
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 fois, utilisez l'option INTO de l'instruction SELECT.
Dans sa portée, la variable table est accessible de la même façon qu'une table normale. Ainsi, la variable table peut servir de table à laquelle des lignes doivent être ajoutées dans une instruction INSERT. Pour plus d'informations, voir table.
Un nom en quatre parties, dont la partie nom de serveur est établie à l'aide de la fonction OPENDATASOURCE, peut être utilisé en tant que source de table dans toutes les situations où un nom de table peut être inclus dans une instruction INSERT.
Les colonnes créées à l'aide du type de données uniqueidentifier stockent les valeurs de type binaire au format 16 octets. Contrairement à ce qui se passe avec les colonnes d'identité, SQL Server 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 y compris les tirets, 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 correcte pour une variable ou une colonne uniqueidentifier. Utilisez la fonction NEWID() afin d'obtenir un GUID (identificateur global unique).
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, voir SET ANSI_PADDING
Ce tableau illustre l'opération par défaut lorsque SET ANSI_PADDING comporte la valeur 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 le niveau de compatibilité de la base de données est inférieur à 70, la valeur est convertie en un espace simple. Pour plus d'informations, voir sp_dbcmptlevel.
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 SQL Server 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, voir 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 des valeurs sont insérées dans des tables SQL Server distantes et que certaines valeurs ne sont pas spécifiées pour toutes les colonnes, l'utilisateur doit identifier les colonnes pour 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. En outre, cette option n'est pas prise en charge pour les instructions INSERT portant sur les tables distantes sous SQL Server 2000 lorsque le niveau de compatibilité ascendante a pour valeur 80.
Lorsqu'un déclencheur INSTEAD-OF est défini sur les actions INSERT dans une table ou une vue, il est exécuté au lieu de l'instruction INSERT. Les versions antérieures de SQL Server ne prennent en charge que les déclencheurs AFTER définis sur les instructions INSERT et les autres instructions de modification des données.
Lorsqu'une instruction INSERT rencontre une erreur arithmétique (dépassement de capacité, de division par zéro ou erreur de domaine) lors de l'évaluation de l'expression, SQL Server 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 renvoyé.
Autorisations
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 des rôles sysadmin, db_owner et db_securityadmin, ainsi que le propriétaire de la table peuvent attribuer des autorisations aux autres utilisateurs.
Exemple
A. Utilisation d'une instruction INSERT simple
Cet exemple crée une table T1 et insère une ligne.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
CREATE TABLE T1 ( column_1 int, column_2 varchar(30))
INSERT T1 VALUES (1, 'Row #1')
B. Insertion de données qui ne sont pas dans le même ordre que les colonnes
Cet exemple utilise column_list et la liste VALUES afin de spécifier de manière explicite les valeurs insérées dans chaque colonne.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
CREATE TABLE T1 ( column_1 int, column_2 varchar(30))
INSERT T1 (column_2, column_1) VALUES ('Row #1',1)
C. Insertion de données de valeurs inférieures à celles des colonnes
Cet exemple crée une table avec quatre colonnes. Les instructions INSERT insèrent des lignes qui contiennent des valeurs de certaines colonnes mais pas de toutes.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
CREATE TABLE T1
( column_1 int identity,
column_2 varchar(30)
CONSTRAINT default_name DEFAULT ('column default'),
column_3 int NULL,
column_4 varchar(40)
)
INSERT INTO T1 (column_4)
VALUES ('Explicit value')
INSERT INTO T1 (column_2,column_4)
VALUES ('Explicit value', 'Explicit value')
INSERT INTO T1 (column_2,column_3,column_4)
VALUES ('Explicit value',-44,'Explicit value')
SELECT *
FROM T1
D. Chargement de données dans une table qui comprend 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 supplante 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é.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
CREATE TABLE T1 ( column_1 int IDENTITY, column_2 varchar(30))
INSERT T1 VALUES ('Row #1')
INSERT T1 (column_2) VALUES ('Row #2')
SET IDENTITY_INSERT T1 ON
INSERT INTO T1 (column_1,column_2)
VALUES (-99,'Explicit identity value')
SELECT *
FROM T1
E. Chargement de données dans une table via une vue
L'instruction INSERT de cet exemple spécifie un nom de vue. 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.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'V1')
DROP VIEW 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)
SELECT *
FROM T1
F. Chargement de données à l'aide de l'option DEFAULT VALUES
L'instruction CREATE TABLE de cet exemple définit chaque colonne avec une valeur qui peut être utilisée lorsqu'aucune valeur explicite n'a été spécifiée dans l'instruction INSERT. L'option DEFAULT VALUES de l'instruction INSERT sert à ajouter des lignes sans fournir de valeurs explicites.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
CREATE DEFAULT bound_default AS 'Bound default value'
GO
CREATE TABLE T1
( column_1 int identity,
column_2 varchar(30)
CONSTRAINT default_name DEFAULT ('column default'),
column_3 timestamp,
column_4 varchar(30),
column_5 int NULL)
GO
USE master
EXEC sp_bindefault 'bound_default','T1.column_4'
INSERT INTO T1 DEFAULT VALUES
SELECT *
FROM T1
G. Chargement 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 chargement 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 une instruction SELECT pour extraire directement les données de la table source (authors) et stocker le jeu de résultats dans la table author_sales. La seconde 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.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'author_sales')
DROP TABLE author_sales
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'get_author_sales' AND type = 'P')
DROP PROCEDURE get_author_sales
GO
USE pubs
CREATE TABLE author_sales
( data_source varchar(20),
au_id varchar(11),
au_lname varchar(40),
sales_dollars smallmoney
)
GO
CREATE PROCEDURE get_author_sales
AS
SELECT 'PROCEDURE', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id like '8%'
GROUP BY authors.au_id, authors.au_lname
GO
--INSERT...SELECT example
USE pubs
INSERT author_sales
SELECT 'SELECT', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id LIKE '8%'
GROUP BY authors.au_id, authors.au_lname
--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
--INSERT...EXECUTE('string') example
INSERT author_sales
EXECUTE
('
SELECT ''EXEC STRING'', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id like ''8%''
GROUP BY authors.au_id, authors.au_lname
')
--Show results.
SELECT * FROM author_sales
H. Insertion de données à l'aide de la clause TOP dans une instruction SELECT
Étant donné qu'une instruction SELECT peut être spécifiée dans une instruction INSERT, la clause TOP peut également être utilisée dans l'instruction SELECT. L'exemple insère les 10 premiers auteurs de la table authors dans une nouvelle table appelée new_authors.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'new_authors')
DROP TABLE new_authors
GO
USE pubs
CREATE TABLE new_authors
(
au_id id,
au_lname varchar(40),
au_fname varchar(20),
phone char(12),
address varchar(40),
city varchar(20),
state char(2),
zip char(5),
contract bit
)
INSERT INTO new_authors
SELECT TOP 10 *
FROM authors
Voir aussi
CREATE TABLE
EXECUTE
FROM
IDENTITY (Propriété)
NEWID
SELECT
SET ROWCOUNT
©1988-2000 Microsoft Corporation. Tous droits réservés.
|
![[:joce]](https://forum-images.hardware.fr/images/perso/joce.gif "[:joce]")
)
![[:rofl2]](https://forum-images.hardware.fr/images/perso/rofl2.gif "[:rofl2]")
![[:spamafote]](https://forum-images.hardware.fr/images/perso/spamafote.gif "[:spamafote]")
)
![[:petrus75]](https://forum-images.hardware.fr/images/perso/petrus75.gif "[:petrus75]")
![[:itm]](https://forum-images.hardware.fr/images/perso/itm.gif "[:itm]")