Dela via

Använda en formatfil för massimport av data (SQL Server)

  • Artikel

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

Den här artikeln illustrerar användningen av en formatfil i massimportåtgärder. En formatfil mappar datafilens fält till kolumnerna i tabellen. Mer information finns i Skapa en formatfil (SQL Server).

Innan du börjar

Exempel på testvillkor

Exemplen på formatfiler i det här avsnittet baseras på tabellen och datafilen som definieras nedan.

Exempeltabell

Skriptet nedan skapar en testdatabas och en tabell med namnet myFirstImport. Kör följande Transact-SQL i 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
   );

Exempeldatafil

Använd Anteckningar, skapa en tom fil D:\BCP\myFirstImport.bcp och infoga följande data:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

Du kan också köra följande PowerShell-skript för att skapa och fylla i datafilen:

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;

Skapa formatfilerna

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.

Skapa en fil som inte är XML-format

Granska filer (icke-XML-format) i SQL Server för detaljerad information. Följande kommando använder verktyget bcp för att generera en fil som inte är xml-format, myFirstImport.fmt, baserat på schemat för myFirstImport. Om du vill använda ett bcp-kommando för att skapa en formatfil anger du argumentet format och använder nul- i stället för en datafilsökväg. Formatalternativet kräver också alternativet -f. I det här exemplet används dessutom c för att ange teckendata, t, används för att ange ett kommatecken som ett fältavslutning, 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.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.fmt

Filen med icke-XML-format bör D:\BCP\myFirstImport.fmt se ut så här:

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

Viktig

Se till att filen som inte är XML-format slutar med en vagnretur\radfeed. Annars får du troligen följande felmeddelande:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Skapa en XML-formatfil

För mer detaljerad information, se XML-formatfiler (SQL Server). Följande kommando använder verktyget bcp för att skapa en XML-formatfil, myFirstImport.xml, baserat på schemat för myFirstImport. Om du vill använda ett bcp-kommando för att skapa en formatfil anger du argumentet format och använder nul- i stället för en datafilsökväg. Formatalternativet kräver alltid alternativet -f och för att skapa en XML-formatfil måste du också ange alternativet -x. I det här exemplet används dessutom c för att ange teckendata, t, används för att ange ett kommatecken som ett fältavgränsare, och T- används för att ange en betrodd anslutning med integrerat säkerhetsstöd. I en kommandotolk anger du följande kommando:

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

REM Review file
Notepad D:\BCP\myFirstImport.xml

XML-formatfilen D:\BCP\myFirstImport.xml bör se ut så här:

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

Använda en formatfil för att massimportera data

I exemplen nedan används databasen, datafilen och formatfilerna som skapades ovan.

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

I en kommandotolk anger du följande kommando:

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"

Använd bcp och XML-formatfil

I en kommandotolk anger du följande kommando:

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

Använd BULK INSERT och icke-XML-formatfil

Kör följande Transact-SQL i 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;

Använd BULK INSERT och XML Formatfil

Kör följande Transact-SQL i 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;

Använd OPENROWSET(BULK...) och icke-XML-formatfil

Kör följande Transact-SQL i 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;

Använd OPENROWSET(BULK...) och XML-formatfil

Kör följande Transact-SQL i 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;

Fler exempel

Skapa en formatfil (SQL Server)
Använd en formatfil för att hoppa över en tabellkolumn (SQL Server)
Använda en formatfil för att hoppa över ett datafält (SQL Server)
Använda en formatfil för att mappa tabellkolumner till Data-File fält (SQL Server)

Nästa steg