Använda en formatfil för att hoppa över ett datafält (SQL Server)

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

En datafil kan innehålla fler fält än antalet kolumner i tabellen. I den här artikeln beskrivs hur du ändrar både icke-XML- och XML-formatfiler för att hantera en datafil med fler fält genom att mappa tabellkolumnerna till motsvarande datafält och ignorera de extra fälten.

Mer information finns i Skapa en formatfil med bcp (SQL Server).

Anteckning

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

Anteckning

Denna syntax, inklusive bulkinsättning, 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å exempeltabellen myTestSkipField och datafilen D:\BCP\myTestSkipField.bcp. Ändra den lokala filplatsen i kodexemplet till en filplats på datorn.

Exempeltabell

Skriptet skapar en testdatabas och en tabell med namnet myTestSkipField. Kör följande Transact-SQL i Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

Exempeldatafil

Skapa en tom fil D:\BCP\myTestSkipField.bcp och infoga följande data:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Skapa formatfilerna

Om du vill massimportera data från myTestSkipField.bcp till tabellen myTestSkipField måste formatfilen göra följande:

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

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, myTestSkipField.fmt, baserat på schemat för myTestSkipField. 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Ändra filen med icke-XML-format

Granska struktur för filer som inte är XML-format för terminologi. Öppna D:\BCP\myTestSkipField.fmt i Anteckningar och utför följande ändringar:

1. Kopiera hela formatfilraden för FirstName och klistra in den direkt efter FirstName på nästa rad.
2. Öka värdet för fältordningen för värdfilen med en för den nya raden och alla efterföljande rader.
3. Öka antalet kolumner för att återspegla det faktiska antalet fält i datafilen.
4. Ändra serverkolumnordningen från 2 till 0 för den andra formatfilraden.

Jämför de ändringar som gjorts:

före

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

efter

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

Den ändrade formatfilen återspeglar nu:

• 4 datafält
• Det första datafältet i myTestSkipField.bcp mappas till den första kolumnen, myTestSkipField.. PersonID
• Det andra datafältet i myTestSkipField.bcp mappas inte till någon kolumn.
• Det tredje datafältet i myTestSkipField.bcp mappas till den andra kolumnen, myTestSkipField.. FirstName
• Det fjärde datafältet i myTestSkipField.bcp mappas till den tredje kolumnen myTestSkipField.. LastName

Skapa en XML-formatfil

Mer information finns i XML-formatfiler (SQL Server). Följande kommando använder verktyget bcp för att skapa en XML-formatfil, myTestSkipField.xml, baserat på schemat för myTestSkipField.

• Kvalificeraren c används för att ange teckendata
• t, används för att ange ett kommatecken som en fältavgränsare
• 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.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

Ändra XML-formatfilen

Granska schemasyntax för XML-formatfiler för terminologi. Öppna D:\BCP\myTestSkipField.xml i Anteckningar och utför följande ändringar:

1. Kopiera hela det andra fältet och klistra in det direkt efter det andra fältet på nästa rad.
2. Öka FIELD ID-värdet med 1 för det nya FIELD och för varje efterföljande FIELD.
3. Öka värdet med COLUMN SOURCE 1 för FirstNameoch LastName för att återspegla den ändrade mappningen.

Jämför de ändringar som gjorts:

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="\r\n" MAX_LENGTH="30" 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"/>
</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="25" 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="30" 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="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

Den ändrade formatfilen återspeglar nu:

• 4 datafält
• FIELD 1 som motsvarar COLUMN 1 mappas till den första tabellkolumnen, myTestSkipField.. PersonID
• FIELD 2 motsvarar inte någon COLUMN och därför mappas inte till någon tabellkolumn.
• FIELD 3 som motsvarar COLUMN 3 mappas till den andra tabellkolumnen, myTestSkipField.. FirstName
• FIELD 4 som motsvarar COLUMN 4 mappas till den tredje tabellkolumnen, myTestSkipField.. LastName

Importera data med en formatfil för att hoppa över ett datafält

Exemplen använder exempeldatabasen, datafilen och formatfilerna som skapas i den här artikeln.

Använd bcp-- och filformat som inte är XML-format

I en kommandotolk anger du följande kommando:

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

Använda bcp- och XML-formatfiler (SQL Server)

I en kommandotolk anger du följande kommando:

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

Använd BULK INFÖRSEL (Transact-SQL) och icke-XML-formatfil

Kör följande Transact-SQL i Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Använd BULK INSERT (Transact-SQL) och XML-formatfiler (SQL Server)

Kör följande Transact-SQL i Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Använd OPENROWSET BULK (Transact-SQL) och en fil med icke-XML-format

Kör följande Transact-SQL i Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

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

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

Använd OPENROWSET BULK - (Transact-SQL) och XML-formatfiler (SQL Server)

Kör följande Transact-SQL i Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Relaterat innehåll

• bcp-verktyg
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Använd en formatfil för att hoppa över en tabellkolumn (SQL Server)
• Använd en formatfil för att mappa tabellkolumner till datafilfält (SQL Server)