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

 

CETTE RUBRIQUE S’APPLIQUE À : ouiSQL Server (à partir de la version 2008)ouiAzure SQL DatabaseouiAzure SQL Data WarehouseouiParallel Data Warehouse

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.

Contour
Conservation des valeurs Null
Utilisation des valeurs par défaut avec l’instruction INSERT ... SELECT * FROM OPENROWSET(BULK...)
Exemples de conditions de test
 ● Exemple de table
 ● Exemple de fichier de données
 ● Exemple de fichier de format non XML
Conserver les valeurs NULL ou utiliser la valeur 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

                                                                                                                                                                                                                  

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.

CommandQualificateurType de qualificateur
bcp-kCommutateur
BULK INSERTKEEPNULLS*Argument
INSERT ... SELECT * FROM OPENROWSET(BULK...)NéantNéant

*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.

System_CAPS_ICON_note.jpg 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 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.

System_CAPS_ICON_note.jpg Remarque

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

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 suivante 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.fmt basé 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

System_CAPS_ICON_important.jpg 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).

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

Argument KEEPNULLS. Exécutez l’instruction Transact-SQL suivante 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

Arguments KEEPNULLS et FORMATFILE. Exécutez l’instruction Transact-SQL suivante 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 suivante 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

Argument FORMATFILE. Exécutez l’instruction Transact-SQL suivante 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

Argument FORMATFILE. Exécutez l’instruction Transact-SQL suivante 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 table KEEPDEFAULTS et argument FORMATFILE. Exécutez l’instruction Transact-SQL suivante 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;

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

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

Ajouts de la communauté

AJOUTER
Afficher: