Een indelingsbestand gebruiken om gegevens bulksgewijs te importeren (SQL Server)

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Dit artikel illustreert het gebruik van een indelingsbestand in bulkimportbewerkingen. Een indelingsbestand wijst de velden van het gegevensbestand toe aan de kolommen van de tabel. Raadpleeg Een indelingsbestand (SQL Server) maken voor meer informatie.

Voordat u begint

• Voor een indelingsbestand dat werkt met een Unicode-tekengegevensbestand, moeten alle invoervelden Unicode-teksttekenreeksen zijn (dat wil zeggen, vaste grootte of door tekens beëindigde Unicode-tekenreeksen).
• Als u SQLXML- gegevens bulksgewijs wilt exporteren of importeren, gebruikt u een van de volgende gegevenstypen in uw indelingsbestand:
  • SQLCHAR of SQLVARCHAR (de gegevens worden verzonden op de clientcodepagina of op de codepagina die wordt bepaald door de sortering)
  • SQLNCHAR of SQLNVARCHAR (de gegevens worden verzonden als Unicode)
  • SQLBINARY of SQLVARBIN (de gegevens worden zonder conversie verzonden).
• Azure SQL Database en Azure Synapse Analytics bieden alleen ondersteuning voor bcp-. Zie voor meer informatie:
  • gegevens laden in Azure Synapse Analytics-
  • Gegevens uit SQL Server laden in Azure Synapse Analytics (platte bestanden)
  • Uw gegevens migreren
• Deze syntaxis, inclusief bulksgewijs invoegen, wordt niet ondersteund in Azure Synapse Analytics. In Azure Synapse Analytics en andere integraties van het clouddatabaseplatform kunt u gegevensverplaatsing uitvoeren via de COPY-instructie in Azure Data Factoryof met behulp van T-SQL-instructies zoals COPY INTO en PolyBase.

Voorbeeldtestvoorwaarden

De voorbeelden van indelingsbestanden in dit onderwerp zijn gebaseerd op de tabel en het gegevensbestand dat hieronder is gedefinieerd.

Voorbeeldtabel

Met het onderstaande script maakt u een testdatabase en een tabel met de naam myFirstImport. Voer de volgende Transact-SQL uit in 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
   );

Voorbeeldgegevensbestand

Maak met Kladblok een leeg bestand D:\BCP\myFirstImport.bcp en voeg de volgende gegevens in:

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

U kunt ook het volgende PowerShell-script uitvoeren om het gegevensbestand te maken en te vullen:

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;

Creëer de opmaakbestanden

SQL Server ondersteunt twee typen indelingsbestand: niet-XML-indeling en XML-indeling. De niet-XML-indeling is de oorspronkelijke indeling die wordt ondersteund door eerdere versies van SQL Server.

Een bestand met een niet-XML-indeling maken

Beoordeel Non-XML bestandsindelingen (SQL Server) voor gedetailleerde informatie. Met de volgende opdracht wordt het hulpprogramma bcp gebruikt om een bestand met een niet-XML-indeling te genereren, myFirstImport.fmt, op basis van het schema van myFirstImport. Als u een bcp-opdracht wilt gebruiken om een indelingsbestand te maken, specificeert u het argument voor de-indeling en gebruikt u nul in plaats van een pad naar een gegevensbestand. Voor de indelingsoptie is ook de optie -f vereist. Bovendien wordt in dit voorbeeld de kwalificatie c gebruikt voor het opgeven van tekengegevens, t, wordt gebruikt om een komma op te geven als een veldeindtekenen T- wordt gebruikt om een vertrouwde verbinding op te geven met geïntegreerde beveiliging. Voer bij een opdrachtprompt de volgende opdracht in:

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

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

Het bestand met de niet-XML-indeling moet er als volgt uitzien D:\BCP\myFirstImport.fmt:

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

Belangrijk

Zorg ervoor dat uw bestand in niet-XML-indeling eindigt met een carriage return\line feed. Anders krijgt u waarschijnlijk het volgende foutbericht:

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

Een XML-indelingsbestand maken

Bekijk SQL Server- (XML Format Files) voor gedetailleerde informatie. Met de volgende opdracht wordt het hulpprogramma bcp gebruikt om een XML-indelingsbestand te maken, myFirstImport.xml, op basis van het schema van myFirstImport. Als u een bcp-opdracht wilt gebruiken om een indelingsbestand te maken, geeft u de -indeling op argument en gebruikt u nul- in plaats van een pad naar een gegevensbestand. Voor de indelingsoptie is altijd de optie -f vereist en om een XML-indelingsbestand te maken, moet u ook de optie -x opgeven. Bovendien wordt in dit voorbeeld de kwalificatie c gebruikt voor het opgeven van tekengegevens, t, wordt gebruikt om een komma op te geven als een veldeindtekenen T- wordt gebruikt om een vertrouwde verbinding op te geven met geïntegreerde beveiliging. Voer bij een opdrachtprompt de volgende opdracht in:

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

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

Je XML-indelingsbestand, D:\BCP\myFirstImport.xml, moet er als volgt uitzien:

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

Een indelingsbestand gebruiken om gegevens bulksgewijs te importeren

In de onderstaande voorbeelden worden de hierboven gemaakte database-, gegevensbestand- en indelingsbestanden gebruikt.

Gebruik bcp en niet-XML-formaatbestand

Voer bij een opdrachtprompt de volgende opdracht in:

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"

Gebruik bcp en XML-indelingsbestand

Voer bij een opdrachtprompt de volgende opdracht in:

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

Gebruik BULK INSERT- en bestand met een niet-XML-indeling

Voer de volgende Transact-SQL uit in 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;

Gebruik BULK INSERT en XML-formaatbestand

Voer de volgende Transact-SQL uit in 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;

Gebruik OPENROWSET(BULK...)- en bestand met niet-XML-indeling

Voer de volgende Transact-SQL uit in 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;

Gebruik OPENROWSET(BULK...) en XML Format File

Voer de volgende Transact-SQL uit in 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;

Meer voorbeelden

Een indelingsbestand maken (SQL Server)
Een indelingsbestand gebruiken om een tabelkolom (SQL Server) over te slaan
Een indelingsbestand gebruiken om een gegevensveld (SQL Server) over te slaan
Een indelingsbestand gebruiken om tabelkolommen toe te wijzen aan Data-File velden (SQL Server)

Volgende stappen

• bcp Hulpprogramma
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• niet-XML-indelingsbestanden (SQL Server)
• XML-indelingsbestanden (SQL Server)
• bestanden opmaken voor het importeren of exporteren van gegevens (SQL Server)