Partager via

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

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

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. Pour plus d’informations, consultez Créer un fichier de format avec bcp (SQL Server).

Conditions préalables

Exemples de conditions de test

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 suivant 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
   );

Exemple de fichier de données

À 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 :

Clear-Host
# revise directory as desired

$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath '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;
Notepad.exe $bcpfile;

Créer les fichiers de format

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.

Remplacez les emplacements de fichiers locaux de l’exemple de code par un emplacement de fichier sur votre ordinateur.

Créer un fichier de format non XML

Consultez Utiliser des 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.fmtbasé 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 plutôt qu’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.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              ""

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

Passez en revue les 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.xmlbasé 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 plutôt qu’un chemin d’accès au fichier de données.
  • L’option de format nécessite toujours l’option -f .
  • Pour créer un fichier de format XML, vous devez également spécifier l’option -x .
  • 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.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="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>

Utiliser un fichier de format pour importer des données 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.

Utiliser bcp et utiliser des fichiers de format non XML (SQL Server)

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

Utiliser des fichiers de format bcp et XML (SQL Server)

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

Utiliser BULK INSERT (Transact-SQL) et utiliser des fichiers de format non XML (SQL Server)

Exécutez l’instruction Transact-SQL suivant 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;

Utiliser BULK INSERT (Transact-SQL) et des fichiers de format XML (SQL Server)

Exécutez l’instruction Transact-SQL suivant 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;

Utiliser OPENROWSET BULK (Transact-SQL) et utiliser des fichiers de format non XML (SQL Server)

Exécutez l’instruction Transact-SQL suivant 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;

Utiliser OPENROWSET BULK (Transact-SQL) et des fichiers de format XML (SQL Server)

Exécutez l’instruction Transact-SQL suivant 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;

Autres exemples

Contenu connexe