Skapa en formatfil med bcp (SQL Server)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Den här artikeln beskriver hur du använder verktyget bcp för att skapa en formatfil för en viss tabell. Formatfilen baseras på det angivna datatypsalternativet (-n, -c, -weller -N) och tabellen eller visningsavgränsarna.

När du massimportera till en SQL Server-tabell eller massexportera data från en tabell kan du använda en formatfil som ett flexibelt system för att skriva datafiler. Formatfiler kräver lite eller ingen redigering för att uppfylla andra dataformat eller för att läsa datafiler från andra program.

Begränsningar

Versionen av verktyget bcp (bcp.exe) som används för att läsa en formatfil måste vara samma som eller senare än den version som användes för att skapa formatfilen. Till exempel kan SQL Server 2016 (13.x) bcp läsa en version 12.0-formatfil, som genereras av SQL Server 2014 (12.x) bcp, men SQL Server 2014 (12.x) bcp inte kan läsa en version 13.0-formatfil som genereras av SQL Server 2016 (13.x) bcp.

Obs

Den här syntaxen, inklusive massinfogning, stöds inte i Azure Synapse Analytics. I Azure Synapse Analytics och andra molndatabasplattformsintegreringar utför du dataflytt via COPY-instruktionen i Azure Data Factoryeller med hjälp av T-SQL-instruktioner som COPY INTO och PolyBase.

Skapa formatfiler

SQL Server stöder två typer av formatfiler: icke-XML-format och XML-format. Icke-XML-format är det ursprungliga format som stöds av tidigare versioner av SQL Server.

I allmänhet är XML- och icke-XML-formatfiler utbytbara. Vi rekommenderar dock att du använder XML-syntax för formatfiler eftersom de ger flera fördelar jämfört med filer som inte är XML-format.

XML-formatfil

Kodexemplen i den här artikeln använder AdventureWorks2022- eller AdventureWorksDW2022-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects. Adventure Works Cycles är ett fiktivt tillverkningsföretag som används för att demonstrera databasbegrepp och scenarier.

Skapa en XML-formatfil

Om du vill använda kommandot bcp för att skapa en formatfil anger du argumentet format och använder nul i stället för en datafilsökväg. Alternativet format kräver alltid alternativet -f och om du vill skapa en XML-formatfil måste du också ange alternativet -x, till exempel bcp <table_or_view> format nul -f <format_file_name> -x.

För att särskilja en XML-formatfil rekommenderar vi att du använder .xml som filnamnstillägg, till exempel MyTable.xml.

Information om strukturen och fälten för XML-formatfiler finns i XML-formatfiler (SQL Server).

Exempel

Det här avsnittet innehåller följande exempel som visar hur du använder bcp-kommandon för att skapa en XML-formatfil. Tabellen HumanResources.Department innehåller fyra kolumner: DepartmentID, Name, GroupNameoch ModifiedDate.

A. Skapa en XML-formatfil för teckendata

I följande exempel skapas en XML-formatfil, Department.xml, för tabellen HumanResources.Department. Formatfilen använder teckendataformat och en icke-standardfältavgränsare (,). Innehållet i den genererade formatfilen visas efter kommandot .

Kommandot bcp innehåller följande kvalificerare.

Kvalificeringsomgångar	Beskrivning
format nul -x -f <format_file>	Anger XML-formatfil.
-c	Anger teckendata.
-t,	Anger ett kommatecken (,) som fältavgränsare. Obs! Om datafilen använder standardfältavgränsaren (\t) är -t växeln onödig.
-T	Anger att verktyget bcp ansluter till SQL Server med en betrodd anslutning med integrerad säkerhet. Om -T inte har angetts måste du ange -U och -P för att logga in.

I Kommandotolken i Windows anger du följande bcp kommando:

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

Den genererade formatfilen Department-c.xmlinnehåller följande XML-element:

<?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>

Information om syntaxen för den här formatfilen finns i XML-formatfiler (SQL Server). Information om teckendata finns i Använda teckenformat för att importera eller exportera data (SQL Server).

B. Skapa en XML-formatfil för interna data

I följande exempel skapas en XML-formatfil, Department-n.xml, för tabellen HumanResources.Department. Formatfilen använder inbyggda datatyper. Innehållet i den genererade formatfilen visas efter kommandot .

Kommandot bcp innehåller följande attribut.

Kvalomgångar	Beskrivning
format nul -x -f <format_file>	Anger XML-formatfil.
-n	Anger inbyggda datatyper.
-T	Anger att verktyget bcp ansluter till SQL Server med en betrodd anslutning med integrerad säkerhet. Om -T inte har angetts måste du ange -U och -P för att logga in.

I Kommandotolken i Windows anger du följande bcp kommando:

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

Den genererade formatfilen Department-n.xmlinnehåller följande XML-element:

<?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>

Information om syntaxen för den här formatfilen finns i XML-formatfiler (SQL Server). Information om hur du använder inbyggda data finns i Använda internt format för att importera eller exportera data (SQL Server).

fil som inte är XML-format

Kodexemplen i den här artikeln använder AdventureWorks2022- eller AdventureWorksDW2022-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects. Adventure Works Cycles är ett fiktivt tillverkningsföretag som används för att demonstrera databasbegrepp och scenarier.

Skapa en fil som inte är XML-format

Om du vill använda kommandot bcp för att skapa en formatfil anger du argumentet format och använder nul i stället för en datafilsökväg. Alternativet format kräver också alternativet -f, till exempel: bcp <table_or_view> format nul -f <format_file_name>.

För att särskilja en fil som inte är XML-format rekommenderar vi att du använder .fmt som filnamnstillägg, till exempel MyTable.fmt.

Information om strukturen och fälten för filer som inte är XML-format finns i Använda filer som inte är XML-format (SQL Server).

Exempel

Det här avsnittet innehåller följande exempel som visar hur du använder bcp-kommandon för att skapa en fil som inte är XML-format. Tabellen HumanResources.Department innehåller fyra kolumner: DepartmentID, Name, GroupNameoch ModifiedDate.

A. Skapa en icke-XML-formatfil för interna data

I följande exempel skapas en XML-formatfil, Department-n.xml, för tabellen HumanResources.Department. Formatfilen använder inbyggda datatyper. Innehållet i den genererade formatfilen visas efter kommandot .

Kommandot bcp innehåller följande kvalificerare.

Kvalificeringar	Beskrivning
format nul -f <format_file>	Anger filen med icke-XML-format.
-n	Anger inbyggda datatyper.
-T	Anger att verktyget bcp ansluter till SQL Server med en betrodd anslutning med integrerad säkerhet. Om -T inte har angetts måste du ange -U och -P för att logga in.

I Kommandotolken i Windows anger du följande bcp kommando:

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

Den genererade formatfilen Department-n.fmtinnehåller följande information:

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

Mer information finns i Använda filer som inte är XML-format (SQL Server).

B. Skapa en icke-XML-formatfil för teckendata

I följande exempel skapas en XML-formatfil, Department.fmt, för tabellen HumanResources.Department. Formatfilen använder teckendataformat och en icke-standardfältavgränsare (,). Innehållet i den genererade formatfilen visas efter kommandot .

Kommandot bcp innehåller följande kvalificerare.

Kvalificeringar	Beskrivning
format nul -f <format_file>	Anger en fil som inte är XML-format.
-c	Anger teckendata.
-T	Anger att verktyget bcp ansluter till SQL Server med en betrodd anslutning med integrerad säkerhet. Om -T inte har angetts måste du ange -U och -P för att logga in.

I Kommandotolken i Windows anger du följande bcp kommando:

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

Den genererade formatfilen Department-c.fmtinnehåller följande information:

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

Mer information finns i Använda filer som inte är XML-format (SQL Server).

C. Skapa en icke-XML-formatfil för unicode-inbyggda data

Om du vill skapa en fil som inte är XML-format för unicode-interna data för HumanResources.Department-tabellen använder du följande kommando:

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

Mer information om hur du använder unicode-inbyggda data finns i Använda unicode-inbyggt format för att importera eller exportera data (SQL Server).

D. Skapa en icke-XML-formatfil för Unicode-teckendata

Om du vill skapa en fil som inte är XML-format för Unicode-teckendata för den HumanResources.Department tabell som använder standardavgränsare använder du följande kommando:

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

Mer information om hur du använder Unicode-teckendata finns i Använda Unicode-teckenformat för att importera eller exportera data (SQL Server).

F. Använd en formatfil med kodsidesalternativet

Om du skapar en formatfil med hjälp av bcp- (dvs. med hjälp av bcp format) skrivs information om sorterings-/kodsidan i formatfilen.

Följande exempelformatfil för en tabell med fem kolumner innehåller sorteringen.

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

Om du försöker importera data till SQL Server med hjälp av bcp in -c -C65001 -f format_file ..." eller "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ...", har information om sorterings-/kodsidan prioritet över alternativet 65001.

Om du genererar en formatfil måste du därför manuellt ta bort sorteringsinformationen från den genererade formatfilen innan du börjar importera data tillbaka till SQL Server.

I följande exempel visas formatfilen utan sorteringsinformation.

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

Mappa datafält till tabellkolumner

Som skapats av bcpbeskriver en formatfil alla tabellkolumner i ordning. Du kan ändra en formatfil för att ordna om eller utelämna tabellrader. Du kan anpassa en formatfil till en datafil vars fält inte mappas direkt till tabellkolumnerna. Mer information finns i följande artiklar:

• Använd en formatfil för att hoppa över en tabellkolumn (SQL Server)
• Använd en formatfil för att hoppa över ett datafält (SQL Server)
• Använd en formatfil för att mappa tabellkolumner till datafilfält (SQL Server)

Relaterat innehåll

• bcp Utility
• Använd icke-XML-formatfiler (SQL Server)
• XML-formatfiler (SQL Server)