Conservation des valeurs NULL ou utilisation des valeurs par défaut lors de l'importation en bloc

Par défaut, lorsque des données sont importées dans une table, la commande bcp et l'instruction BULK INSERT inspectent toutes les valeurs par défaut définies pour les colonnes de la table. Par exemple, s'il y a un champ NULL dans les données d'un fichier, la valeur par défaut pour la colonne est chargée à la place. La commande bcp et l'instruction BULK INSERT vous permettent de spécifier que les valeurs NULL doivent être conservées.

À l'opposé, une instruction INSERT standard conserve la valeur NULL au lieu d'insérer une valeur par défaut. L'instruction INSERT ... SELECT * FROM OPENROWSET(BULK...) présente le même comportement de base que l'instruction INSERT standard mais elle prend également en charge un indicateur de table pour l'insertion des valeurs par défaut.

[!REMARQUE]

Pour obtenir des exemples de fichiers de format qui ignorent une colonne de table, consultez Utilisation d'un fichier de format pour ignorer une colonne de table.

Exemples de table et de fichier de données

Pour exécuter les exemples proposés dans cette rubrique, vous devez créer un exemple de table et un exemple de fichier de données.

Exemple de table

Les exemples requièrent la création d'une table nommée MyTestDefaultCol2 dans l'exemple de base de données AdventureWorks sous le schéma dbo. Pour créer cette table, dans l'éditeur de requêtes MicrosoftSQL Server Management Studio, exécutez le code suivant :

USE AdventureWorks;
GO
CREATE TABLE MyTestDefaultCol2 
(Col1 smallint,
Col2 nvarchar(50) DEFAULT 'Default value of Col2',
Col3 nvarchar(50) 
);
GO

La deuxième colonne de table, Col2, a une valeur par défaut.

Exemple de fichier de format

Certains des exemples d'importation en bloc utilisent un fichier de format non-XML, MyTestDefaultCol2-f-c.Fmt, qui correspond exactement à la table MyTestDefaultCol2. Pour créer ce fichier de format, à l'invite de commandes Microsoft Windows, saisissez le code suivant :

bcp AdventureWorks..MyTestDefaultCol2 format nul -c -f C:\MyTestDefaultCol2-f-c.Fmt -t, -r\n -T

Pour plus d'informations sur la création de fichiers de format, consultez Création d'un fichier de format

Fichier de données d'exemple

L'exemple utilise un fichier de données, MyTestEmptyField2-c.Dat, dont le deuxième champ ne contient aucune valeur. Le fichier de données MyTestEmptyField2-c.Dat contient les enregistrements suivants.

1,,DataField3
2,,DataField3

Conservation des valeurs NULL à l'aide de la commande bcp ou de l'instruction BULK INSERT

Les qualificateurs suivants spécifient qu'un champ vide du fichier de données conserve sa valeur NULL lors de l'importation en bloc, au lieu d'hériter d'une valeur par défaut (s'il y en a une) pour les colonnes de table.

Commande	Qualificateur	Type de qualificateur
bcp	-k	Commutateur
BULK INSERT	KEEPNULLS1	Argument

1 Dans le cas de la commande BULK INSERT, si des valeurs par défaut ne sont pas disponibles, la colonne de table doit être définie de manière à autoriser les valeurs NULL.

[!REMARQUE]

Ces qualificateurs désactivent le contrôle des définitions DEFAULT sur une table par ces commandes d'importation en bloc. Toutefois, pour toute instruction INSERT concurrente, des définitions DEFAULT sont attendues.

Pour plus d'informations, consultez Utilitaire bcp et BULK INSERT (Transact-SQL).

Exemples

Les exemples de cette section réalisent des importations en bloc à l'aide de la commande bcp ou de l'instruction BULK INSERT et conservent les valeurs NULL.

La deuxième colonne de table, Col2, a une valeur par défaut. Le champ correspondant du fichier de données contient une chaîne vide. Par défaut, lorsque la commande bcp ou l'instruction BULK INSERT est utilisée pour importer des données depuis ce fichier de données dans la table MyTestDefaultCol2, la valeur par défaut de la colonne Col2 est insérée et le résultat suivant est obtenu :

1	Default value of Col2	DataField3
2	Default value of Col2	DataField3

Pour insérer « NULL » au lieu de « Default value of Col2 », vous devez utiliser le commutateur -k ou l'option KEEPNULL, comme le montrent les exemples suivants de la commande bcp et de l'instruction BULK INSERT.

Utilisation de la commande bcp et conservation des valeurs NULL

L'exemple suivant montre comment conserver les valeurs NULL à l'aide d'une commande bcp. La commande bcp a les commutateurs suivants :

Commutateur	Description
-f	La commande utilise un fichier de format.
-k	Lors de l'opération, les colonnes vides doivent garder une valeur NULL, au lieu de prendre une valeur par défaut pour les colonnes insérées.
-T	L'utilitaire bcp se connecte à SQL Server à l'aide d'une connexion approuvée.

À l'invite de commandes Windows, entrez :

bcp AdventureWorks..MyTestDefaultCol2 in C:\MyTestEmptyField2-c.Dat -f C:\MyTestDefaultCol2-f-c.Fmt -k -T

Utilisation de l'instruction BULK INSERT et conservation des valeurs NULL

L'exemple suivant montre comment utiliser l'option KEEPNULLS dans une instruction BULK INSERT. Depuis un outil de requête, tel que l'éditeur de requêtes SQL Server Management Studio, exécutez le code suivant :

USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
   FROM 'C:\MyTestEmptyField2-c.Dat'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      KEEPNULLS
   );
GO

Conservation des valeurs par défaut à l'aide de l'instruction INSERT ... SELECT * FROM OPENROWSET(BULK...).

Par défaut, l'instruction INSERT ... SELECT * FROM OPENROWSET(BULK...) attribue la valeur NULL à toutes les colonnes qui ne sont pas spécifiées dans l'opération de chargement en bloc. Toutefois, pour un champ vide du fichier de données, vous pouvez spécifier que la colonne de table correspondante utilise sa valeur par défaut (s'il y en a une). Pour utiliser les valeurs par défaut, spécifiez l'indicateur de table suivant :

Commande	Qualificateur	Type de qualificateur
INSERT ... SELECT * FROM OPENROWSET(BULK...).	WITH(KEEPDEFAULTS)	Indicateur de table

[!REMARQUE]

Pour plus d'informations, consultez INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL) et Indicateurs de table (Transact-SQL)

Exemples

L'exemple d'instruction INSERT ... SELECT * FROM OPENROWSET(BULK...) suivant importe des données en bloc et conserve les valeurs par défaut.

Pour exécuter les exemples, vous devez créer l'exemple de table MyTestDefaultCol2, le fichier de données MyTestEmptyField2-c.Dat et utiliser un fichier de format, MyTestDefaultCol2-f-c.Fmt. Pour plus d'informations sur la création de ces exemples, consultez la section « Exemples de table et de fichier de données », plus haut dans cette rubrique.

La deuxième colonne de la table, Col2, a une valeur par défaut. Le champ correspondant du fichier de données contient une chaîne vide. Lorsque l'instruction INSERT ... SELECT * FROM OPENROWSET(BULK...) importe les champs de ce fichier de données dans la table MyTestDefaultCol2, par défaut, la valeur NULL est insérée dans la colonne Col2, au lieu de la valeur par défaut. Ce comportement par défaut produit le résultat suivant :

1	NULL	DataField3
2	NULL	DataField3

Pour insérer la valeur par défaut « Default value of Col2 », au lieu de « NULL », vous devez utiliser l'indicateur de table KEEPDEFAULTS, comme le montre l'exemple suivant. Depuis un outil de requête, tel que l'éditeur de requêtes SQL Server Management Studio, exécutez le code suivant :

USE AdventureWorks;
GO
INSERT INTO MyTestDefaultCol2
    WITH (KEEPDEFAULTS)
    SELECT *
      FROM OPENROWSET(BULK  'C:\MyTestEmptyField2-c.Dat',
      FORMATFILE='C:\MyTestDefaultCol2-f-c.Fmt'     
      ) as t1 ;
GO

Voir aussi

Référence

BACKUP (Transact-SQL)

OPENROWSET (Transact-SQL)

Utilitaire bcp

BULK INSERT (Transact-SQL)

Indicateurs de table (Transact-SQL)

Concepts

Conservation des valeurs NULL ou utilisation des valeurs par défaut lors de l'importation en bloc

Création et modification des définitions DEFAULT