Een indelingsbestand maken met bcp (SQL Server)

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

In dit artikel wordt beschreven hoe u het hulpprogramma bcp gebruikt om een indelingsbestand voor een bepaalde tabel te maken. Het indelingsbestand is gebaseerd op de opgegeven optie voor gegevenstypen (-n, -c, -wof -N) en de scheidingstekens voor tabellen of weergaven.

Wanneer u bulksgewijs importeert in een SQL Server-tabel of gegevens bulksgewijs exporteert uit een tabel, kunt u een indelingsbestand gebruiken als een flexibel systeem voor het schrijven van gegevensbestanden. Bestandsformaten hebben weinig of geen bewerking nodig om aan andere gegevensindelingen te voldoen of om gegevensbestanden van andere softwareprogramma's te lezen.

Beperkingen

De versie van het hulpprogramma bcp (bcp.exe) dat wordt gebruikt om een indelingsbestand te lezen, moet hetzelfde zijn als of hoger dan de versie die wordt gebruikt om het indelingsbestand te maken. SQL Server 2016 (13.x) bcp kan bijvoorbeeld een bestand met de 12.0-indeling lezen, die wordt gegenereerd door SQL Server 2014 (12.x) bcp, maar SQL Server 2014 (12.x) bcp- geen bestand met versie 13.0-indeling kan lezen, dat wordt gegenereerd door SQL Server 2016 (13.x) bcp.

Notitie

Deze syntaxis, inclusief bulksgewijs invoegen, wordt niet ondersteund in Azure Synapse Analytics. In Azure Synapse Analytics en andere integraties van het clouddatabaseplatform kunt u gegevensverplaatsing uitvoeren via de COPY-instructie in Azure Data Factoryof met behulp van T-SQL-instructies zoals COPY INTO en PolyBase.

Indelingsbestanden maken

SQL Server ondersteunt twee typen indelingsbestand: niet-XML-indeling en XML-indeling. De niet-XML-indeling is de oorspronkelijke indeling die wordt ondersteund door eerdere versies van SQL Server.

Over het algemeen zijn XML- en niet-XML-bestanden uitwisselbaar. We raden u echter aan XML-syntaxis te gebruiken voor het opmaken van bestanden, omdat ze verschillende voordelen bieden ten opzichte van bestanden die niet-XML-indeling zijn.

XML-indelingsbestand

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase, die u kunt downloaden van de Microsoft SQL Server-voorbeelden en communityprojecten startpagina. Adventure Works Cycles is een fictief productiebedrijf dat wordt gebruikt om databaseconcepten en -scenario's te demonstreren.

Een XML-indelingsbestand maken

Als u een bcp opdracht wilt gebruiken om een indelingsbestand te maken, geeft u het argument format op en gebruikt u nul in plaats van een pad naar een gegevensbestand. De optie format vereist altijd de -f optie en om een XML-indelingsbestand te maken, moet u ook de optie -x opgeven, zoals bcp <table_or_view> format nul -f <format_file_name> -x.

Als u een XML-indelingsbestand wilt onderscheiden, raden we u aan .xml te gebruiken als bestandsnaamextensie, bijvoorbeeld MyTable.xml.

Voor meer informatie over de structuur en velden van XML-bestanden, zie XML Format Files (SQL Server).

Voorbeelden

Deze sectie bevat de volgende voorbeelden die laten zien hoe u de opdrachten bcp kunt gebruiken om een XML-indelingsbestand te maken. De tabel HumanResources.Department bevat vier kolommen: DepartmentID, Name, GroupNameen ModifiedDate.

Een. Een XML-indelingsbestand maken voor tekengegevens

In het volgende voorbeeld wordt een XML-indelingsbestand, Department.xml, gemaakt voor de tabel HumanResources.Department. Het indelingsbestand maakt gebruik van tekengegevensindelingen en een niet-standaardveldeindteken (,). De inhoud van het gegenereerde indelingsbestand wordt weergegeven na de opdracht.

De opdracht bcp bevat de volgende kwalificaties.

Kwalificaties	Beschrijving
format nul -x -f <format_file>	Geeft het XML-indelingsbestand op.
-c	Geeft tekengegevens op.
-t,	Hiermee specificeert u een komma (,) als veldseparator. Opmerking: Als het gegevensbestand gebruikmaakt van het standaardveldeindteken (\t), is de -t-schakeloptie niet nodig.
-T	Hiermee geeft u op dat het hulpprogramma bcp verbinding maakt met SQL Server met een vertrouwde verbinding met behulp van geïntegreerde beveiliging. Als -T niet is opgegeven, moet u -U en -P opgeven om u aan te melden.

Voer bij de Windows-opdrachtprompt de volgende bcp opdracht in:

bcp AdventureWorks2022.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T

Het gegenereerde indelingsbestand, Department-c.xml, bevat de volgende XML-elementen:

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Voor informatie over de syntaxis van dit indelingsbestand, zie XML-indelingsbestanden (SQL Server). Zie Tekenindeling gebruiken voor het importeren of exporteren van gegevens (SQL Server)voor meer informatie over tekengegevens.

B. Een XML-indelingsbestand maken voor systeemeigen gegevens

In het volgende voorbeeld wordt een XML-indelingsbestand, Department-n.xml, gemaakt voor tabel HumanResources.Department. Het indelingsbestand maakt gebruik van systeemeigen gegevenstypen. De inhoud van het gegenereerde indelingsbestand wordt weergegeven na de opdracht.

De opdracht bcp bevat de volgende kwalificaties.

Kwalificaties	Beschrijving
format nul -x -f <format_file>	Specificeert het XML-indelingsbestand.
-n	Hiermee geeft u systeemeigen gegevenstypen op.
-T	Hiermee geeft u op dat het hulpprogramma bcp verbinding maakt met SQL Server met een vertrouwde verbinding met behulp van geïntegreerde beveiliging. Als -T niet is opgegeven, moet u -U en -P opgeven om u aan te melden.

Voer bij de Windows-opdrachtprompt de volgende bcp opdracht in:

bcp AdventureWorks2022.HumanResources.Department format nul -x -f Department-n.xml -n -T

Het gegenereerde indelingsbestand, Department-n.xml, bevat de volgende XML-elementen:

<?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="NativeFixed" LENGTH="2"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NativeFixed" LENGTH="8"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Zie XML-indelingsbestanden (SQL Server)voor meer informatie over de syntaxis van dit indelingsbestand. Zie Systeemeigen indeling gebruiken voor het importeren of exporteren van gegevens (SQL Server)voor informatie over het gebruik van systeemeigen gegevens.

bestand met een niet-XML-indeling

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase, die u kunt downloaden van de Microsoft SQL Server-voorbeelden en communityprojecten startpagina. Adventure Works Cycles is een fictief productiebedrijf dat wordt gebruikt om databaseconcepten en -scenario's te demonstreren.

Een bestand met een niet-XML-indeling maken

Als u een bcp opdracht wilt gebruiken om een indelingsbestand te maken, geeft u het argument format op en gebruikt u nul in plaats van een pad naar een gegevensbestand. Voor de optie format is ook de optie -f vereist, zoals: bcp <table_or_view> format nul -f <format_file_name>.

Als u een bestand met een niet-XML-indeling wilt onderscheiden, wordt u aangeraden .fmt te gebruiken als bestandsnaamextensie, bijvoorbeeld MyTable.fmt.

Zie Niet-XML-indelingsbestanden (SQL Server) gebruikenvoor meer informatie over de structuur en velden van niet-XML-indelingsbestanden.

Voorbeelden

Deze sectie bevat de volgende voorbeelden die laten zien hoe u bcp opdrachten gebruikt om een bestand met een niet-XML-indeling te maken. De tabel HumanResources.Department bevat vier kolommen: DepartmentID, Name, GroupNameen ModifiedDate.

Een. Een niet-XML-indelingsbestand maken voor systeemeigen gegevens

In het volgende voorbeeld wordt een XML-indelingsbestand, Department-n.xml, gemaakt voor de tabel HumanResources.Department. Het indelingsbestand maakt gebruik van systeemeigen gegevenstypen. De inhoud van het gegenereerde indelingsbestand wordt weergegeven na de opdracht.

De opdracht bcp bevat de volgende kwalificaties.

Kwalificatoren	Beschrijving
format nul -f <format_file>	Hiermee specificeert u het niet-XML-formaatbestand.
-n	Hiermee geeft u systeemeigen gegevenstypen op.
-T	Hiermee geeft u op dat het hulpprogramma bcp verbinding maakt met SQL Server met een vertrouwde verbinding met behulp van geïntegreerde beveiliging. Als -T niet is opgegeven, moet u -U en -P opgeven om u aan te melden.

Voer bij de Windows-opdrachtprompt de volgende bcp opdracht in:

bcp AdventureWorks2022.HumanResources.Department format nul -T -n -f Department-n.fmt

Het gegenereerde indelingsbestand, Department-n.fmt, bevat de volgende informatie:

12.0
4
1  SQLSMALLINT   0       2       ""   1     DepartmentID         ""
2  SQLNCHAR      2       100     ""   2     Name                 SQL_Latin1_General_CP1_CI_AS
3  SQLNCHAR      2       100     ""   3     GroupName            SQL_Latin1_General_CP1_CI_AS
4  SQLDATETIME   0       8       ""   4     ModifiedDate         ""

Zie Niet-XML-indelingsbestanden (SQL Server) gebruikenvoor meer informatie.

B. Een niet-XML-indelingsbestand maken voor tekengegevens

In het volgende voorbeeld wordt een XML-indelingsbestand gemaakt, Department.fmt, voor de HumanResources.Department tabel. Het indelingsbestand maakt gebruik van tekengegevensindelingen en een niet-standaardveldeindteken (,). De inhoud van het gegenereerde indelingsbestand wordt weergegeven na de opdracht.

De opdracht bcp bevat de volgende kwalificaties.

Kwalificaties	Beschrijving
format nul -f <format_file>	Dit specificeert een bestand met een niet-XML-formaat.
-c	Specificeert tekengegevens.
-T	Hiermee geeft u op dat het hulpprogramma bcp verbinding maakt met SQL Server met een vertrouwde verbinding met behulp van geïntegreerde beveiliging. Als -T niet is opgegeven, moet u -U en -P opgeven om u aan te melden.

Voer bij de Windows-opdrachtprompt de volgende bcp opdracht in:

bcp AdventureWorks2022.HumanResources.Department format nul -c -f Department-c.fmt -T

Het gegenereerde indelingsbestand, Department-c.fmt, bevat de volgende informatie:

12.0
4
1  SQLCHAR       0       7       "\t"     1     DepartmentID            ""
2  SQLCHAR       0       100     "\t"     2     Name                    SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR       0       100     "\t"     3     GroupName               SQL_Latin1_General_CP1_CI_AS
4  SQLCHAR       0       24      "\r\n"   4     ModifiedDate            ""

Voor meer informatie, zie Niet-XML-indelingsbestanden (SQL Server) gebruiken.

C. Een niet-XML-indelingsbestand maken voor systeemeigen Unicode-gegevens

Als u een bestand met een niet-XML-indeling wilt maken voor systeemeigen Unicode-gegevens voor de HumanResources.Department tabel, gebruikt u de volgende opdracht:

bcp AdventureWorks2022.HumanResources.Department format nul -T -N -f Department-n.fmt

Zie Unicode Native Format gebruiken voor het importeren of exporteren van gegevens (SQL Server)voor meer informatie over het gebruik van systeemeigen Unicode-gegevens.

D. Een niet-XML-indelingsbestand maken voor Unicode-tekengegevens

Als u een niet-XML-indelingsbestand wilt maken voor Unicode-tekengegevens voor de HumanResources.Department tabel die gebruikmaakt van standaardeindtekens, gebruikt u de volgende opdracht:

bcp AdventureWorks2022.HumanResources.Department format nul -T -w -f Department-w.fmt

Zie Unicode-tekenindeling gebruiken voor het importeren of exporteren van gegevens (SQL Server)voor meer informatie over het gebruik van Unicode-tekengegevens.

F. Gebruik een formaatbestand met de codepagina-optie

Als u een indelingsbestand maakt met behulp van bcp- (met behulp van bcp format), wordt informatie over de sorterings-/codepagina geschreven in het indelingsbestand.

Het volgende voorbeeld van een bestandsindeling voor een tabel met vijf kolommen bevat de sorteervolgorde.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0          Cyrillic_General_CS_AS
2  SQLCHAR         0       0       "\t"             2     c_1          Cyrillic_General_CS_AS
3  SQLCHAR         0       3000    "\t"             3     c_2          Cyrillic_General_CS_AS
4  SQLCHAR         0       5       "\t"             4     c_3          ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""

Als u gegevens probeert te importeren in SQL Server met behulp van bcp in -c -C65001 -f format_file ..." of "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ...", informatie over de sorterings-/codepagina heeft voorrang boven de optie 65001.

Als u daarom een indelingsbestand genereert, moet u de sorteringsgegevens handmatig verwijderen uit het gegenereerde indelingsbestand voordat u begint met het importeren van gegevens in SQL Server.

In het volgende voorbeeld ziet u het indelingsbestand zonder de sorteringsgegevens.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0              ""
2  SQLCHAR         0       0       "\t"             2     c_1              ""
3  SQLCHAR         0       3000    "\t"             3     c_2              ""
4  SQLCHAR         0       5       "\t"             4     c_3              ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""

Gegevensvelden toewijzen aan tabelkolommen

Zoals gemaakt door bcp, worden in een indelingsbestand alle tabelkolommen in volgorde beschreven. U kunt een indelingsbestand wijzigen om tabelrijen opnieuw te rangschikken of weg te laten. U kunt een opmaakbestand aanpassen aan een gegevensbestand waarvan de velden niet rechtstreeks overeenkomen met de tabelkolommen. Zie de volgende artikelen voor meer informatie:

• Een indelingsbestand gebruiken om een tabelkolom (SQL Server) over te slaan
• Een indelingsbestand gebruiken om een gegevensveld (SQL Server) over te slaan
• Een indelingsbestand gebruiken om tabelkolommen toe te wijzen aan gegevensbestandsvelden (SQL Server)

Verwante inhoud

• bcp Utility
• niet-XML-indelingsbestanden (SQL Server) gebruiken
• XML-indelingsbestanden (SQL Server)