Adatmező kihagyása formátumfájllal (SQL Server)

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

Az adatfájlok több mezőt tartalmazhatnak, mint a tábla oszlopainak száma. Ez a cikk azt ismerteti, hogyan módosíthatja a nem XML- és XML-formátumú fájlokat úgy, hogy a táblaoszlopokat a megfelelő adatmezőkre írják le, és figyelmen kívül hagyják a további mezőket.

További információ: Formátumfájl létrehozása bcp-vel (SQL Server).

Jegyzet

XML vagy nem XML formátumú fájlok használhatók egy adatfájl tömeges importálására a táblába egy bcp segédprogram parancs, a BULK INSERT (Transact-SQL) utasítás vagy az INSERT ... SELECT * FROM OPENROWSET BULK (Transact-SQL) utasítás segítségével. További információ: Adatok tömeges importálása (SQL Server)formátumfájl használata.

Jegyzet

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 cikkben szereplő módosított formátumfájlok példái a mintatáblán myTestSkipField és az adatfájlon D:\BCP\myTestSkipField.bcpalapulnak. Módosítsa a kódrészlet helyi fájlhelyét egy fájl helyére a számítógépén.

Mintatábla

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

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

Mintaadatfájl

Hozzon létre egy üres fájlt D:\BCP\myTestSkipField.bcp, és szúrja be a következő adatokat:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Formátumfájlok létrehozása

Ahhoz, hogy tömegesen importálja az adatokat a myTestSkipField.bcp-ból a myTestSkipField táblába, a formátumfájlnak a következőket kell tennie:

• Az első adatmezőt az első oszlopra képezze le, PersonID.
• Hagyja ki a második adatmezőt.
• Képezze le a harmadik adatmezőt a második oszlopra, FirstName.
• A negyedik adatmezőt képezd le a harmadik oszlopra, LastName.

A formátumfájl létrehozásának legegyszerűbb módja a bcp segédprogram. Először hozzon létre egy alapformátumfájlt a meglévő táblából. Másodszor módosítsa az alapformátumfájlt úgy, hogy az tükrözze a tényleges adatfájlt.

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, myTestSkipField.fmta myTestSkipFieldsémája alapján. Emellett a minősítő c karakteradatok megadására szolgál, t, vesszőt ad meg mezőkifejezésként, a T pedig 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

A nem XML formátumú fájl módosítása

A terminológiához tekintse át a nem XML formátumú fájlok szerkezetét. Nyissa meg D:\BCP\myTestSkipField.fmt a Jegyzettömbben, és hajtsa végre a következő módosításokat:

1. Másolja ki a FirstName formátumfájljának teljes sorát, és illessze be közvetlenül FirstName után a következő sorba.
2. Növelje a gazdagépfájl mező sorrend értékét egy értékkel az új sorhoz és az összes további sorhoz.
3. Növelje az oszlopok számát, hogy tükrözze az adatfájlban lévő mezők tényleges számát.
4. Módosítsa a kiszolgáló oszlopainak sorrendjét a 2-ról 0-re a második formátumfájl sorában.

Hasonlítsa össze a végrehajtott módosításokat:

előtt

13.0
3
1       SQLCHAR    0       7       ","      1     PersonID        ""
2       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

után

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID     ""
2       SQLCHAR    0       25      ","      0     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       50      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

A módosított formátumfájl a következőt tükrözi:

• 4 adatmező
• A myTestSkipField.bcp első adatmezője az első oszlopra van leképezve myTestSkipField.. PersonID.
• A myTestSkipField.bcp második adatmezője nincs egyetlen oszlophoz sem megfeleltetve.
• A myTestSkipField.bcp harmadik adatmezője a második oszlopra van leképezve, myTestSkipField.. FirstName
• Az myTestSkipField.bcp negyedik adatmezője a harmadik oszlopra van leképezve, myTestSkipField.. LastName

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, myTestSkipField.xmla myTestSkipFieldsémája alapján.

• A minősítő c karakteradatok megadására szolgál
• t, egy vessző mezőhatároló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 x minősítőt xml-alapú formátumfájl létrehozásához kell használni.

A parancssorban adja meg a következő parancsot:

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

Az XML-formátumfájl módosítása

Tekintse át xml formátumú fájlok sémaszintaxisát, a terminológiát. Nyissa meg D:\BCP\myTestSkipField.xml a Jegyzettömbben, és hajtsa végre a következő módosításokat:

1. Másolja ki a teljes második mezőt, és illessze be közvetlenül a második mező után a következő sorba.
2. Növelje az FIELD ID értéket 1-gyel az újhoz FIELD és minden továbbihoz FIELD.
3. Növelje az COLUMN SOURCE értéket 1-sel FirstName, és LastName tükrözze a módosított leképezést.

Hasonlítsa össze a végrehajtott módosításokat:

előtt

<?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="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

után

<?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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

A módosított formátumfájl a következőt tükrözi:

• 4 adatmező
• FIELD Az 1-nek COLUMN megfelelő 1 az első táblaoszlopra van megfeleltetve, myTestSkipField.. PersonID
• FIELD A 2 nem felel meg egyik COLUMN oszlopnak sem, ezért egyik táblaoszlopra sem van leképezve.
• FIELD A 3-nak COLUMN megfelelő 3 a második táblaoszlopra van megfeleltetve, myTestSkipField.. FirstName
• FIELD A 4-nek COLUMN megfelelő 4 a harmadik táblaoszlopra van megfeleltetve, myTestSkipField.. LastName

Adatok importálása formátumfájllal egy adatmező kihagyásához

A példák a cikkben létrehozott mintaadatbázist, adatfájlt és formátumfájlokat használják.

A bcp és a nem-XML formátumú fájl használata

A parancssorban adja meg a következő parancsot:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

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

A parancssorban adja meg a következő parancsot:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

A BULK INSERT (Transact-SQL) és a nem XML formátumú fájl használata

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

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 myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

OPENROWSET BULK (Transact-SQL) és nem XML formátumú fájl használata

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

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 myTestSkipField;
INSERT INTO dbo.myTestSkipField  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Kapcsolódó tartalom

• bcp segédprogram
• TÖMEGES BESZÚRÁS (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Táblaoszlop (SQL Server) kihagyása formátumfájllal
• Táblázatoszlopok adatfájlmezőkbe (SQL Server) való leképezése formátumfájllal