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)
Cet article illustre l’utilisation d’un fichier de format dans les opérations d’importation 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 (SQL Server).
Avant de commencer
- 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 SQLVARCHAR (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 SQLVARBIN (les données sont envoyées sans être converties).
- Azure SQL Database et Azure Synapse Analytics ne prennent en charge que bcp. Si vous souhaitez en savoir plus, veuillez consulter :
- Cette syntaxe, y compris l’insertion en bloc, n’est pas prise en charge dans Azure Synapse Analytics. Dans Azure Synapse Analytics et d’autres intégrations de plateforme de base de données cloud, effectuez le déplacement des données via l’instruction COPY dans Azure Data Factory ou à l’aide d’instructions T-SQL telles que COPY INTO et PolyBase.
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 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
);
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.
Créer un fichier de format non XML
Pour plus d’informations, consultez Fichiers de format non XML (SQL Server). 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 à 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 ""
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éer un fichier de format XML
Pour plus d’informations, consultez Fichiers de format XML (SQL Server). 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 à 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="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 le fichier de format bcp et 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"
Utiliser le fichier de format bcp et 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;"
Utiliser BULK INSERT et 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;
Utiliser BULK INSERT et le 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;
Utiliser OPENROWSET(BULK...) et 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;
Utiliser OPENROWSET(BULK...) et le 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;
Autres exemples
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)
Étapes suivantes
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour