Conserver les valeurs NULL ou utiliser la valeur par défaut lors de l'importation en bloc (SQL Server)

  • Article

Par défaut, quand 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, si un fichier de données contient un champ NULL, la valeur par défaut de 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 ... L’instruction 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.

Notes

Pour obtenir des exemples de fichiers de format qui ignorent une colonne de table, consultez Utiliser un fichier de format pour ignorer une colonne de table (SQL Server).

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 Microsoft SQL Server Management Studio Éditeur de requête, exécutez :

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.

Fichier de format d'exemple

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, entrez :

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éer un fichier de format (SQL Server).

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 Pour BULK INSERT, si les valeurs par défaut ne sont pas disponibles, la colonne de table doit être définie pour autoriser les valeurs Null.

Notes

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 la 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 à partir de 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 ou l’option -k KEEPNULL, comme illustré dans les exemples bcp et BULK INSERT suivants.

Utilisation de la commande bcp et conservation des valeurs NULL

L’exemple suivant montre comment conserver les valeurs NULL dans une commande bcp . La commande bcp contient les commutateurs suivants :

Commutateur Description
-f La commande utilise un fichier de format.
-k Pendant l’opération, les colonnes vides doivent conserver une valeur NULL et les colonnes insérées ne doivent pas prendre de valeur par défaut.
-T Spécifie que l’utilitaire bcp se connecte à SQL Server avec 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. À partir d’un outil de requête, tel que SQL Server Management Studio Éditeur de requête, exécutez :

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

Conservation des valeurs par défaut avec INSERT ... SELECT * FROM OPENROWSET(BULK...)

Par défaut, toutes les colonnes qui ne sont pas spécifiées dans l’opération de chargement en bloc sont définies sur NULL par INSERT ... SELECT * FROM OPENROWSET(BULK...). Toutefois, vous pouvez spécifier que pour un champ vide dans le fichier de données, la colonne de table correspondante utilise sa valeur par défaut (le cas échéant). 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

Notes

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

Exemples

L’élément INSERT suivant ... L’exemple SELECT * FROM OPENROWSET(BULK...) 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. Quand INSERT ... SELECT * FROM OPENROWSET(BULK...) importez les champs de ce fichier de données dans la table MyTestDefaultCol2 . Par défaut, NULL est inséré dans 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. À partir d’un outil de requête, tel que SQL Server Management Studio Éditeur de requête, exécutez :

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

Tâches associées

Pour utiliser un fichier de format

Pour utiliser des formats de données pour l'importation ou l'exportation en bloc

Pour spécifier des formats de données pour la compatibilité lors de l'utilisation de bcp

Voir aussi

BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL)
Utilitaire bcp
BULK INSERT (Transact-SQL)
Indicateurs de table (Transact-SQL)