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

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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.

En revanche, une instruction régulière INSERT conserve la valeur Null au lieu d’insérer une valeur par défaut. Insert ... L’instruction SELECT * FROM OPENROWSET BULK fournit le même comportement de base que l’instruction INSERT standard, mais prend également en charge un indicateur de table pour l’insertion des valeurs par défaut.

Conserver les 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 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 NULLsur .

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

* Pour BULK INSERT (Transact-SQL), si les valeurs par défaut ne sont pas disponibles, la colonne de table doit être définie pour autoriser les valeurs Null.

Remarque

Ces qualificateurs désactivent la vérification des DEFAULT définitions sur une table par ces commandes d’importation en bloc. Toutefois, pour toutes les instructions simultanées INSERT , DEFAULT les définitions sont attendues.

Utiliser les valeurs par défaut avec 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 des valeurs par défaut, utilisez des indicateurs de table.

Pour plus d’informations, consultez OPENROWSET BULK.

Exemples de conditions de test

Les exemples utilisent les fichiers de base de données et de format créés dans cet article.

Remplacez l’emplacement du fichier local de l’exemple de code par un emplacement de fichier sur votre ordinateur.

Exemple de table

Le script crée une base de données de test et une table nommée myNulls. La quatrième colonne de table, a Kidsune 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
   );

Exemple de fichier de données

À l’aide du Bloc-notes, créez un fichier D:\BCP\myNulls.bcp vide et insérez les exemples de données suivants. 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. Pour plus d’informations, consultez Utiliser des fichiers de format non XML (SQL Server).

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 au lieu d’un chemin d’accès au fichier de données.
• L’option de format nécessite également l’option -f.
• c est utilisé pour spécifier des données de caractère
• t, est utilisé pour spécifier une virgule comme marque de fin de champ
• 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 avec bcp (SQL Server).

Conserver des valeurs null ou utiliser des valeurs par défaut pendant l’importation en bloc

Les exemples utilisent la base de données, le fichier de données et les fichiers de format créés dans cet article.

Utiliser bcp et conserver 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;"

Utiliser bcp et conserver des valeurs Null avec un fichier de format non XML

Commutateurs -k et -f commutateurs.

À 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;"

Utiliser bcp et les 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;"

Utiliser des valeurs bcp et 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;"

Utiliser BULK INSERT et conserver des valeurs Null sans fichier de format

Argument KEEPNULLS.

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;

Utiliser BULK INSERT et conserver des valeurs Null avec un fichier de format non XML

Et KEEPNULLS l’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
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myNulls.fmt',
        KEEPNULLS
        );

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

Utiliser BULK INSERT et utiliser 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;

Utiliser BULK INSERT et les valeurs par défaut avec un fichier de format non XML

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
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myNulls.fmt'
        );

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

Utiliser OPENROWSET BULK et conserver des valeurs Null avec un fichier de format non XML

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
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myNulls.bcp', 
        FORMATFILE = 'D:\BCP\myNulls.fmt'  
        ) AS t1;

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

Utiliser OPENROWSET BULK et conserver les valeurs par défaut avec un fichier de format non XML

Indicateur KEEPDEFAULTS de table et FORMATFILE argument.

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 à une exportation ou une importation en bloc

Pour utiliser un fichier de format

• Créer un fichier de format avec bcp (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 de format natif et de 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 les indicateurs de fin de champ et de ligne (SQL Server)

• Spécifier la longueur du préfixe dans les fichiers de données à l’aide de bcp (SQL Server)

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

Contenu connexe

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