Conserver les valeurs null ou les valeurs par défaut lors de l’importation en bloc (SQL Server)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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.

Plan

Conservation des valeurs Null Utilisation des valeurs par défaut avec l’instruction INSERT ... SELECT * FROM OPENROWSET(BULK...) Exemples de conditions de test  ● Échantillon de table  ● Exemple de fichier de données  ● Échantillon de fichier de format non XML Conserver les valeurs NULL ou utiliser les valeurs par défaut lors de l’importation en bloc  ● Utilisation de la commande bcp et conservation des valeurs Null sans fichier de format  ● Utilisation de la commande bcp et conservation des valeurs Null avec un fichier de format non XML  ● Utilisation de la commande bcp et utilisation des valeurs par défaut sans fichier de format  ● Utilisation de la commande bcp et utilisation des valeurs par défaut avec un fichier de format non XML  ● Utilisation de BULK INSERT et conservation des valeurs Null sans fichier de format  ● Utilisation de BULK INSERT et conservation des valeurs Null avec un fichier de format non XML  ● Utilisation de BULK INSERT et utilisation des valeurs par défaut sans fichier de format  ● Utilisation de BULK INSERT et utilisation des valeurs par défaut avec un fichier de format non XML  ● Utilisation d’ OPENROWSET(BULK...) et conservation des valeurs Null avec un fichier de format non XML  ● Utilisation d’ OPENROWSET(BULK...) et utilisation des valeurs par défaut avec un fichier de format non XML

Conservation des valeurs Null

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. Pour OPENROWSET, par défaut, les colonnes qui ne sont pas spécifiées dans l’opération de chargement en bloc sont définies avec la valeur Null.

Commande	Qualificateur	Type de qualificateur
bcp	-k	Switch
BULK INSERT	KEEPNULLS*	Argument
INSERT ... SELECT * FROM OPENROWSET(BULK...)	N/A	N/A

* Dans le cas de 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.

Utilisation des valeurs par défaut avec l’instruction INSERT ... SELECT * FROM OPENROWSET(BULK...)

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, utilisez l’indicateur de table KEEPDEFAULTS.

Remarque

Pour plus d’informations, consultez INSERT (Transact-SQL),SELECT (Transact-SQL), OPENROWSET (Transact-SQL) et Table Hints (Transact-SQL)

Exemples de conditions de test

Les exemples de cette rubrique sont fondés sur la table, le fichier de données et le fichier de format définis ci-dessous.

Exemple de table

Le script ci-dessous crée une base de données de test et une table nommée myNulls. La quatrième colonne de table, Kids, a une valeur par défaut. Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNulls ( 
   PersonID smallint not null,
   FirstName varchar(25),
   LastName varchar(30),
   Kids varchar(13) DEFAULT 'Default Value',
   BirthDate date
   );

Fichier de données d’exemple

À l’aide du Bloc-notes, créez un fichier vide D:\BCP\myNulls.bcp et insérez les données ci-dessous. Notez qu’il n’existe aucune valeur dans le troisième enregistrement, quatrième colonne.

1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,1963-11-14
3,Stella,Rosenhain,,1992-03-02

Vous pouvez aussi exécuter le script PowerShell suivant pour créer et remplir le fichier de données :

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'MyNulls.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

Exemple de fichier de format non XML

SQL Server prend en charge deux types de fichier de format : format XML et format non XML. Le format non XML est le format d’origine pris en charge dans les versions précédentes de SQL Server. Veuillez consulter Fichiers de format non XML (SQL Server) pour obtenir des informations détaillées. La commande suivante utilise l’utilitaire bcp pour générer un fichier de format non xml myNulls.fmtbasé sur le schéma de myNulls. Pour utiliser une commande bcp pour créer un fichier de format, spécifiez l’argument format et utilisez nul à la place d’un chemin de fichier de données. L’option format nécessite également l’option -f . Pour cet exemple, le qualificateur c est utilisé pour spécifier les données de caractère, t est utilisé pour spécifier une virgule comme délimiteur de champ, et T est utilisé pour spécifier une connexion approuvée à l’aide de la sécurité intégrée. À partir d'une invite de commandes, entrez la commande suivante :

bcp TestDatabase.dbo.myNulls format nul -c -f D:\BCP\myNulls.fmt -t, -T

REM Review file
Notepad D:\BCP\myNulls.fmt

Important

Vérifiez que votre fichier de format non XML se termine par un retour charriot\saut de ligne. Sinon, vous recevez probablement le message d’erreur suivant :

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Pour plus d’informations sur la création de fichiers de format, consultez Créer un fichier de format (SQL Server).

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

Les exemples ci-dessous utilisent la base de données, le fichier de données et les fichiers de format créés ci-dessus.

Utilisation de la commande bcp et conservation des valeurs Null sans fichier de format

Commutateur-k . À partir d'une invite de commandes, entrez la commande suivante :

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Utilisation de la commande bcp et conservation des valeurs Null avec un fichier de format non XML

Commutateurs-k et -f . À partir d'une invite de commandes, entrez la commande suivante :

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Utilisation de la commande bcp et utilisation des valeurs par défaut sans fichier de format

À partir d'une invite de commandes, entrez la commande suivante :

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Utilisation de la commande bcp et utilisation des valeurs par défaut avec un fichier de format non XML

Commutateur-f . À partir d'une invite de commandes, entrez la commande suivante :

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Utilisation de BULK INSERT et conservation des valeurs Null sans fichier de format

ArgumentKEEPNULLS . Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
	FROM 'D:\BCP\myNulls.bcp'
	WITH (
		DATAFILETYPE = 'char',  
		FIELDTERMINATOR = ',',  
		KEEPNULLS
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Utilisation de BULK INSERT et conservation des valeurs Null avec un fichier de format non XML

ArgumentsKEEPNULLS et FORMATFILE . Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myNulls.fmt',
		KEEPNULLS
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Utilisation de BULK INSERT et utilisation des valeurs par défaut sans fichier de format

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
	  );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Utilisation de BULK INSERT et utilisation des valeurs par défaut avec un fichier de format non XML

ArgumentFORMATFILE . Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myNulls.fmt'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Utilisation d’ OPENROWSET(BULK...) et conservation des valeurs Null avec un fichier de format non XML

ArgumentFORMATFILE . Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myNulls.bcp', 
		FORMATFILE = 'D:\BCP\myNulls.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Utilisation d’ OPENROWSET(BULK...) et utilisation des valeurs par défaut avec un fichier de format non XML

Indicateur de tableKEEPDEFAULTS et argument FORMATFILE . Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
WITH (KEEPDEFAULTS) 
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myNulls.bcp', 
		FORMATFILE = 'D:\BCP\myNulls.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Tâches associées

• Conserver des valeurs d'identité lors de l'importation de données en bloc (SQL Server)

• Préparer des données en vue d’une exportation ou d’une importation en bloc (SQL Server)

Pour utiliser un fichier de format

• Créer un fichier de format (SQL Server)

• Utiliser un fichier de format pour importer des données en bloc (SQL Server)

• Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server)

• Utiliser un fichier de format pour ignorer un champ de données (SQL Server)

• Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)

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

• Importer des données au format natif et caractère à partir de versions antérieures de SQL Server

• Utiliser le format caractère pour importer ou exporter des données (SQL Server)

• Utiliser le format natif pour importer ou exporter des données (SQL Server)

• Utiliser le format caractère Unicode pour importer ou exporter des données (SQL Server)

• Utiliser le format natif Unicode pour importer ou exporter des données (SQL Server)

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

• Spécifier des indicateurs de fin de champ et de fin de ligne (SQL Server)

• Spécifier une longueur de préfixe dans des fichiers de données à l'aide de bcp (SQL Server)

• Spécifier le type de stockage de fichiers à l'aide de bcp (SQL Server)

Voir aussi

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