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

 

S’applique à : SQL Server 2016

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

Cette rubrique illustre l'utilisation d'un fichier de format dans les importations en bloc. Un fichier de format met en relation les champs du fichier de données avec les colonnes de la table. Veuillez consulter Créer un fichier de format (SQL Server) pour plus d’informations.

Contour
Avant de commencer
Exemples de conditions de test
 ● Exemple de table
 ● Fichier de données d’exemple
Création des fichiers de format
 ● Création d’un fichier de format non-XML
 ● Création d’un fichier de format XML
Utilisation d'un fichier de format pour importer des données en bloc
 ● Utilisation d’un fichier de format bcp et non-XML
 ● Utilisation de bcp et d’un fichier de format XML
 ● Utilisation de BULK INSERT et d’un fichier de format non-XML
 ● Utilisation de BULK INSERT et d’un fichier de format XML
 ● Utilisation d’OPENROWSET(BULK...) et d’un fichier de format non-XML
 ● Utilisation d’OPENROWSET(BULK...) et d’un fichier de format XML

                                                                                                                                                                                                                  

  • Pour qu’un fichier de format fonctionne avec un fichier de données de caractères Unicode, tous les champs d’entrée doivent être des chaînes de texte Unicode (autrement dit, des chaînes Unicode de taille fixe ou terminées par un caractère).
  • Pour exporter ou importer en bloc des données SQLXML, utilisez l’un des types de données ci-dessous dans votre fichier de format.
    • SQLCHAR ou SQLVARYCHAR (les données sont envoyées dans la page de codes du client ou dans la page de codes impliquée par le classement)
    • SQLNCHAR ou SQLNVARCHAR (les données sont envoyées au format Unicode)
    • SQLBINARY ou SQLVARYBIN (les données sont envoyées sans être converties).
  • Azure SQL Database et Azure SQL Data Warehouse prennent uniquement en charge bcp. Pour plus d’informations, consultez :

Les fichiers de format pris en exemple dans cette rubrique sont fondés sur la table et le fichier de données 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 myFirstImport. Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management Studio (SSMS) :

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

Fichier de données d'exemple

À l’aide du Bloc-notes, créez un fichier vide D:\BCP\myFirstImport.bcp et insérez les données suivantes :

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,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 + 'MyFirstImport.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,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

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

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.

Création d'un fichier de format non-XML

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 myFirstImport.fmt basé sur le schéma de myFirstImport. 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.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T

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

Votre fichier de format non XML D:\BCP\myFirstImport.fmt doit se présenter comme suit :

13.0
4
1       SQLCHAR             0       7       ","      1     PersonID               ""
2       SQLCHAR             0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       11      "\r\n"   4     BirthDate              ""

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

Création d'un fichier de format XML

Veuillez consulter Fichiers de format XML (SQL Server) pour obtenir des informations détaillées. La commande suivante utilise l’utilitaire bcp pour créer un fichier de format xml myFirstImport.xml basé sur le schéma de myFirstImport. Pour utiliser une commande bcp permettant de 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 de format nécessite toujours l’option -f. De plus, pour créer un fichier de format XML, vous devez spécifier l’option -x. 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.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.xml

Votre fichier de format XML D:\BCP\myFirstImport.xml doit se présenter comme suit :

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
 </ROW>
</BCPFORMAT>

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 bcp et d’un fichier de format non-XML

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

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

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

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

Utilisation de bcp et d’un fichier de format XML

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

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

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

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

Utilisation de BULK INSERT et d’un fichier de format non-XML

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

USE TestDatabase;  
GO

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

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

Utilisation de BULK INSERT et d’un fichier de format XML

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

USE TestDatabase;  
GO

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

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

Utilisation d’OPENROWSET(BULK...) et d’un fichier de format non-XML

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

USE TestDatabase;
GO

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

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

Utilisation d’OPENROWSET(BULK...) et d’un fichier de format XML

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

USE TestDatabase;  
GO

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

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

Créer un fichier de format (SQL Server)
Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)
Utiliser un fichier de format pour ignorer un champ de données (SQL Server)
Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server)

Utilitaire bcp
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Fichiers de format non-XML (SQL Server)
Fichiers de format XML (SQL Server)
Fichiers de format pour l'importation ou l'exportation de données (SQL Server)

Ajouts de la communauté

AJOUTER
Afficher: