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

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. Se Skapa en formatfil med bcp (SQL Server) för mer information.

Förutsättningar

• Om du vill köra T-SQL-kommandon på SQL Server-instansen använder du SQL Server Management Studio (SSMS),MSSQL-tillägget för Visual Studio Code, sqlcmd eller ditt favoritverktyg för T-SQL-frågor.

• För att en formatfil ska fungera med en Unicode-teckendatafil måste alla indatafält vara Unicode-textsträngar (det vill säga antingen unicode-strängar med fast storlek eller teckenredigerade Unicode-strängar).

• Om du vill massexportera eller importera exempel på massimport och export av XML-dokumentdata (SQL Server) använder du någon av följande datatyper i formatfilen:

  • SQLCHAR eller SQLVARCHAR (data skickas på klientens kodsida eller på kodsidan som antyds av sorteringen)
  • SQLNCHAR eller SQLNVARCHAR (data skickas som Unicode)
  • SQLBINARY eller SQLVARBIN (data skickas utan konvertering).

• Azure SQL Database och Azure Synapse Analytics stöder endast bcp. Ytterligare information finns i:

  • Ladda data i Azure Synapse Analytics
  • Läsa in data från SQL Server till Azure Synapse Analytics (flata filer)
  • Migrera dina data

• 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å 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.

Ändra kodexemplets lokala filplatser till en filplats på datorn.

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, myFirstImport.fmt, baserat på schemat för myFirstImport.

• Om du vill använda ett bcp-kommando för att skapa en formatfil anger du format argumentet och använder nul i stället för en sökväg för datafiler.
• Formatalternativet kräver också alternativet -f.
• 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.

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

Mer information finns i 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 format argumentet och använder nul i stället för en sökväg för datafiler.
• Formatalternativet kräver alltid alternativet -f .
• Om du vill skapa en XML-formatfil måste du också ange alternativet -x .
• 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.

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 Använd filer som inte är XML-format (SQL Server)

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ända bcp- och XML-formatfiler (SQL Server)

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 (Transact-SQL) och Använd filer som inte är XML-format (SQL Server)

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 (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 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 (Transact-SQL) och Använd filer som inte är XML-format (SQL Server)

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 - (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 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 med bcp (SQL Server)
• 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-verktyg
• BULK INSERT (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• Använd filer som inte är XML-format (SQL Server)
• XML-formatfiler (SQL Server)
• Formatera filer för att importera eller exportera data (SQL Server)