Dela via

Använd en formatfil för att mappa tabellkolumner till datafilfält (SQL Server)

  • Artikel

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

En datafil kan innehålla fält ordnade i en annan ordning än motsvarande kolumner i tabellen. I den här artikeln visas både icke-XML- och XML-formatfiler som har ändrats för att hantera en datafil vars fält är ordnade i en annan ordning än tabellkolumnerna. Den ändrade formatfilen mappar datafälten till motsvarande tabellkolumner. Se Skapa en formatfil med bcp (SQL Server) för mer information.

Antingen kan en icke-XML- eller XML-formatfil användas för att massimportera en datafil till tabellen med hjälp av ett bcp-verktyg kommando, BULK INSERT--instruktion eller INSERT ... SELECT * FROM OPENROWSET-instruktion. Mer information finns i Använda en formatfil för att massimportera data (SQL Server).

Not

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.

Exempel på testvillkor

Exemplen på ändrade formatfiler i den här artikeln baseras på tabellen och datafilen som definieras senare i den här artikeln.

Exempeltabell

Följande Transact-SQL skript skapar en testdatabas och en tabell med namnet myRemap.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

Exempeldatafil

Följande data visar FirstName och LastName i omvänd ordning enligt tabellen myRemap. Med hjälp av en textredigerare som Anteckningar skapar du en tom fil D:\BCP\myRemap.bcp och infogar följande data:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Skapa formatfilerna

Om du vill massimportera data från myRemap.bcp till tabellen myRemap måste formatfilen utföra följande åtgärder:

  • Mappa det första datafältet till den första kolumnen PersonID.
  • Mappa det andra datafältet till den tredje kolumnen, LastName.
  • Mappa det tredje datafältet till den andra kolumnen, FirstName.
  • Mappa det fjärde datafältet till den fjärde kolumnen Gender.

Den enklaste metoden för att skapa formatfilen är med hjälp av bcp-verktyget. Skapa först en basformatfil från den befintliga tabellen. För det andra ändrar du basformatfilen så att den återspeglar den faktiska datafilen.

Skapa en fil som inte är XML-format

Se Använd icke-XML-formatfiler (SQL Server) för detaljerad information. Följande kommando använder verktyget bcp för att generera en fil som inte är XML-format, myRemap.fmt, baserat på schemat för myRemap. Dessutom används c för att ange teckendata, t, används för att ange ett kommatecken som fältavgränsare och T används för att ange en betrodd anslutning med integrerad säkerhet. I en kommandotolk anger du följande kommando:

bcp TestDatabase.dbo.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T

Ändra filen med icke-XML-format

Information om terminologi finns i Structure of Non-XML Format Files. Öppna D:\BCP\myRemap.fmt i Anteckningar och utför följande ändringar:

  1. Ordna om ordningen på format-filraderna så att raderna är i samma ordning som data i myRemap.bcp.
  2. Kontrollera att fältordningsvärdena för värdfilen är sekventiella.
  3. Kontrollera att det finns en vagnretur efter den sista format-filraden.

Jämför ändringarna:

före

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       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

efter

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

Den ändrade formatfilen återspeglar nu:

  • Det första datafältet i myRemap.bcp mappas till den första kolumnen, myRemap.. PersonID
  • Det andra datafältet i myRemap.bcp mappas till den tredje kolumnen, myRemap.. LastName
  • Det tredje datafältet i myRemap.bcp mappas till den andra kolumnen, myRemap.. FirstName
  • Det fjärde datafältet i myRemap.bcp mappas till den fjärde kolumnen myRemap.. Gender

Skapa en XML-formatfil

Granska XML-formatfiler (SQL Server) för detaljerad information. Följande kommando använder verktyget bcp för att skapa en XML-formatfil, myRemap.xml, baserat på schemat för myRemap. Dessutom används c för att ange teckendata, t, används för att ange ett kommatecken som fältavgränsare och T används för att ange en betrodd anslutning med integrerad säkerhet. x-kvalificeraren måste användas för att generera en XML-baserad formatfil. I en kommandotolk anger du följande kommando:

bcp TestDatabase.dbo.myRemap format nul -c -x -f D:\BCP\myRemap.xml -t, -T

Ändra XML-formatfilen

Läs Schemasyntax för XML-formatfiler för terminologi. Öppna D:\BCP\myRemap.xml i Anteckningar och utför följande ändringar:

  1. I vilken ordning de <FIELD> elementen deklareras i formatfilen är den ordning i vilken fälten visas i datafilen. Ändra därför ordningen för de <FIELD> elementen med ID-attributen 2 och 3.

  2. Kontrollera att attributvärdena för <FIELD> ID är sekventiella.

  3. Ordningen på de <COLUMN> elementen i elementet <ROW> definierar i vilken ordning massåtgärden skickar dem till målet. XML-formatfilen tilldelar varje <COLUMN> element ett lokalt namn som inte har någon relation till kolumnen i måltabellen för en massimportåtgärd. Ordningen på de <COLUMN> elementen är oberoende av ordningen på <FIELD> element i en <RECORD-> definition. Varje <COLUMN>-element motsvarar ett <FIELD> -element (vars ID anges i attributet SOURCE för <COLUMN>-elementet). Därför är värdena för <COLUMN> SOURCE de enda attribut som kräver revision. Ångra ordningen för <COLUMN> SOURCE-attributen 2 och 3.

Jämför ändringarna:

före

<?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="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</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="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

efter

<?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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

Den ändrade formatfilen återspeglar nu:

  • FÄLT 1, som motsvarar KOLUMN 1, mappas till den första tabellkolumnen myRemap.. PersonID
  • FÄLT 2, som motsvarar KOLUMN 2, mappas om till den tredje tabellkolumnen myRemap.. LastName
  • FÄLT 3, som motsvarar KOLUMN 3, mappas om till den andra tabellkolumnen myRemap.. FirstName
  • FÄLT 4, som motsvarar KOLUMN 4, mappas till den fjärde tabellkolumnen myRemap.. Gender

Importera data med en formatfil för att mappa tabellkolumner till datafilfält

I följande exempel används databasen, datafilen och formatfilerna som skapades tidigare.

Använda bcp- och icke-XML-formatfil

På kommandotolken skriver du in följande kommando.

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T

Använda bcp- och XML-formatfil

I en kommandotolk skriver du in följande kommando.

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.xml -T

Använd BULK INSERT och icke-XML-formatfil

Kör följande Transact-SQL skript.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
    WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');
GO

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

Använd BULK INSERT och XML-formatfil

Kör följande Transact-SQL skript.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
    WITH (FORMATFILE = 'D:\BCP\myRemap.xml');
GO

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

Använda OPENROWSET- och icke-XML-formatfil

Kör följande Transact-SQL skript.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
    BULK 'D:\BCP\myRemap.bcp',
    FORMATFILE = 'D:\BCP\myRemap.fmt'
) AS t1;
GO

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

Använda OPENROWSET- och XML-formatfilen

Kör följande Transact-SQL skript.

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
    BULK 'D:\BCP\myRemap.bcp',
    FORMATFILE = 'D:\BCP\myRemap.xml'
) AS t1;
GO

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

Relaterat innehåll