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. Bekijk Een indelingsbestand maken met bcp (SQL Server) voor meer informatie.

Vereiste voorwaarden

• Als u T-SQL-opdrachten wilt uitvoeren op uw SQL Server-exemplaar, gebruikt u SQL Server Management Studio (SSMS), de MSSQL-extensie voor Visual Studio Code, sqlcmd of uw favoriete hulpprogramma voor T-SQL-query's.

• 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 voorbeelden van bulksgewijs importeren en exporteren van XML-documenten (SQL Server)- 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 aanvullende 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.

Wijzig de lokale bestandslocaties van het codevoorbeeld in een bestandslocatie op uw computer.

Een bestand met een niet-XML-indeling maken

Raadpleeg Gebruik bestanden in niet-XML-indeling (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, geeft u het format argument op en gebruikt nul u dit in plaats van een pad naar een gegevensbestand.
• Voor de indelingsoptie is ook de -f optie vereist.
• c wordt gebruikt om tekengegevens op te geven
• t, wordt gebruikt om een komma op te geven als een veldeindteken
• 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              ""

Een XML-indelingsbestand maken

Bekijk XML-indelingsbestanden (SQL Server) 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 het format argument op en gebruikt nul u dit in plaats van een pad naar een gegevensbestand.
• Voor de indelingsoptie is altijd de -f optie vereist.
• Als u een XML-indelingsbestand wilt maken, moet u ook de -x optie opgeven.
• c wordt gebruikt om tekengegevens op te geven
• t, wordt gebruikt om een komma op te geven als een veldeindteken
• 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.

BCP gebruiken en niet-XML-indelingsbestanden gebruiken (SQL Server)

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"

BCP- en XML-indelingsbestanden (SQL Server) gebruiken

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

BULK INSERT (Transact-SQL) gebruiken en niet-XML-indelingsbestanden (SQL Server) gebruiken

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;

BULK INSERT (Transact-SQL) en XML-indelingsbestanden (SQL Server) gebruiken

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;

OPENROWSET BULK (Transact-SQL) gebruiken en Bestanden met niet-XML-indeling (SQL Server) gebruiken

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;

OPENROWSET BULK (Transact-SQL) en XML-indelingsbestanden (SQL Server) gebruiken

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 met bcp (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 gegevensbestandsvelden (SQL Server)

Verwante inhoud

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