Adatok tömeges importálása formátumfájl használatával (SQL Server)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Ez a cikk egy formátumfájl tömeges importálási műveletekben való használatát mutatja be. A formátumfájl leképozza az adatfájl mezőit a tábla oszlopaihoz. További információért tekintse át Formátumfájl létrehozása bcp (SQL Server) használatával.

Előfeltételek

• T-SQL-parancsok SQL Server-példányon való futtatásához használja az SQL Server Management Studiót (SSMS), a Visual Studio Code MSSQL-bővítményét, az sqlcmd-et vagy a kedvenc T-SQL-lekérdezési eszközét.

• Ahhoz, hogy egy formátumfájl Unicode-karakteres adatfájllal működjön, minden beviteli mezőnek Unicode szöveges sztringeknek kell lennie (azaz rögzített méretű vagy karakterekkel végződő Unicode-sztringeknek).

• Az XML-dokumentumok (SQL Server) adatainak tömeges importálására és exportálására szolgáló példák tömeges exportálásához vagy importálásához használja a formátumfájlban az alábbi adattípusok egyikét:

  • SQLCHAR vagy SQLVARCHAR (az adatokat az ügyfélkódlapon vagy a rendezés által sugallt kódlapon küldi el)
  • SQLNCHAR vagy SQLNVARCHAR (az adatok Unicode-ként lesznek elküldve)
  • SQLBINARY vagy SQLVARBIN (az adatok konvertálás nélkül lesznek elküldve).

• Az Azure SQL Database és az Azure Synapse Analytics csak a bcp-et támogatják. További információkért lásd:

  • Adatok betöltése az Azure Synapse Analytics
  • Adatok betöltése AZ SQL Serverről az Azure Synapse Analyticsbe (lapos fájlok)
  • Adatok áttelepítése

• Ez a szintaxis, beleértve a tömeges beszúrást is, nem támogatott az Azure Synapse Analyticsben. Az Azure Synapse Analyticsben és más felhőalapú adatbázisplatform-integrációkban az adatáthelyezést az Azure Data Factory COPY utasításánakvagy T-SQL-utasítások, például a COPY INTO és a PolyBasehasználatával végezheti el.

Példa tesztelési feltételekre

A jelen témakörben szereplő formátumfájlok példái az alábbi táblázaton és adatfájlon alapulnak.

Mintatábla

Az alábbi szkript létrehoz egy tesztadatbázist és egy myFirstImportnevű táblát. Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

Mintaadatfájl

A Jegyzettömb használatával hozzon létre egy üres fájlt D:\BCP\myFirstImport.bcp, és szúrja be a következő adatokat:

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

Másik lehetőségként a következő PowerShell-szkriptet is végrehajthatja az adatfájl létrehozásához és feltöltéséhez:

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;

Formátumfájlok létrehozása

Az SQL Server kétféle formátumfájlt támogat: nem XML- és XML-formátumot. A nem XML formátum az SQL Server korábbi verziói által támogatott eredeti formátum.

Módosítsa a kódminta helyi fájlhelyeit fájlhelyre a számítógépen.

Nem XML formátumú fájl létrehozása

Részletes információkért tekintse át Nem XML formátumú fájlok (SQL Server) használatát. Az alábbi parancs a bcp segédprogram használatával hoz létre egy nem xml formátumú fájlt, myFirstImport.fmta myFirstImportsémája alapján.

• Ha bcp-parancsot szeretne használni egy formátumfájl létrehozásához, adja meg az format argumentumot, és használja nul adatfájl elérési útja helyett.
• A formátumbeállításhoz szükség van a -f beállításra is.
• c karakteradatok megadására szolgál
• t,vesszőt mezőelválasztóként való megadására szolgál.
• T megbízható kapcsolat megadására szolgál az integrált biztonság használatával.

A parancssorban adja meg a következő parancsot:

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

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

A nem XML formátumú fájl D:\BCP\myFirstImport.fmt a következőképpen kell kinéznie:

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

Fontos

Győződjön meg arról, hogy a nem XML formátumú fájl egy kocsivissza\sor hírcsatornával végződik. Ellenkező esetben valószínűleg a következő hibaüzenet jelenik meg:

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

XML-formátumfájl létrehozása

Részletes információkért tekintse át az XML-formátumfájlokat (SQL Server ). Az alábbi parancs a bcp segédprogram használatával hoz létre egy xml formátumú fájlt, myFirstImport.xmla myFirstImportsémája alapján.

• Ha bcp-parancsot szeretne használni egy formátumfájl létrehozásához, adja meg az format argumentumot, és használja nul adatfájl elérési útja helyett.
• A formátumbeállításhoz mindig szükség van a beállításra -f .
• XML formátumú fájl létrehozásához meg kell adnia a -x beállítást is.
• c karakteradatok megadására szolgál
• t,vesszőt mezőelválasztóként való megadására szolgál.
• T megbízható kapcsolat megadására szolgál az integrált biztonság használatával.

A parancssorban adja meg a következő parancsot:

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

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

Az XML-formátumfájlnak a D:\BCP\myFirstImport.xml a következőképpen kell kinéznie:

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

Adatok tömeges importálása formátumfájl használatával

Az alábbi példák a fent létrehozott adatbázist, adatfájlt és formátumfájlokat használják.

Bcp és Nem XML formátumú fájlok használata (SQL Server)

A parancssorban adja meg a következő parancsot:

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- és XML-formátumú fájlok használata (SQL Server)

A parancssorban adja meg a következő parancsot:

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

Használja a BULK INSERT (Transact-SQL) és Használja a nem XML formátumú fájlokat (SQL Server)

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (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;

TÖMEGES BESZÚRÁS (Transact-SQL) és XML-formátumú fájlok (SQL Server) használata

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (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) és nem XML formátumú fájlok használata (SQL Server)

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (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) és XML formátumú fájlok (SQL Server) használata

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (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;

További példák

• Formátumfájl létrehozása bcp (SQL Server)
• Táblaoszlop (SQL Server) kihagyása formátumfájllal
• Adatmező (SQL Server) kihagyása formátumfájllal
• Táblázatoszlopok adatfájlmezőkbe (SQL Server) való leképezése formátumfájllal

Kapcsolódó tartalom

• bcp segédprogram
• Tömeges beszúrás (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• Nem XML formátumú fájlok (SQL Server)
• XML-formátumfájlok (SQL Server)
• Fájlok formázása adatok importálásához vagy exportálásához (SQL Server)