Identitásértékek megőrzése adatok tömeges importálásakor (SQL Server)

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

Az identitásértékeket tartalmazó adatfájlok tömegesen importálhatók a Microsoft SQL Server egy példányába.

Alapértelmezés szerint az importált adatfájl identitásoszlopának értékei figyelmen kívül lesznek hagyva, és az SQL Server automatikusan egyedi értékeket rendel hozzá. Az egyedi értékek a tábla létrehozása során megadott mag- és növekményértékeken alapulnak.

Ha az adatfájl nem tartalmazza a tábla azonosító oszlopának értékeit, egy formátumfájl használatával adja meg, hogy a tábla azonosító oszlopát kihagyja az adatok importálása során. További információt a Táblaoszlop kihagyása (SQL Server) formátumfájllal című témakörben talál.

Identitásértékek megőrzése

Ha meg szeretné akadályozni, hogy az SQL Server identitásértékeket rendeljen hozzá, miközben az adatsorokat tömegesen importálja egy táblába, használja a megfelelő keep-identity parancs minősítőt. A keep-identity minősítő megadásakor az SQL Server az adatfájlban lévő identitásértékeket használja.

Ezek a minősítők a következők:

Parancs	Azonosító-megőrzési minősítő	Minősítő típusa
bcp	-E	Kapcsoló
BULK INSERT	KEEPIDENTITY	érvelés
INSERT ... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Táblázatos tipp

További információ: bcp Segédprogram, BULK INSERT (Transact-SQL), OPENROWSET BULK (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), és táblázat utasítások (Transact-SQL).

Megjegyzés:

Ha olyan automatikusan növekvő számot szeretne létrehozni, amely több táblában is használható, vagy amely alkalmazásokból hívható meg anélkül, hogy bármilyen táblára hivatkozik, olvassa el a Sorszámok sorozatot.

Példa tesztelési feltételekre

A jelen témakör példái az alább definiált táblázaton, adatfájlon és formátumfájlon alapulnak.

Mintatábla

Az alábbi szkript létrehoz egy tesztadatbázist és egy myIdentitynevű 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.myIdentity ( 
   PersonID smallint IDENTITY(1,1) NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date
   );

Mintafájl

A Jegyzettömb használatával hozzon létre egy üres fájlt D:\BCP\myIdentity.bcp, és szúrja be az alábbi adatokat.

3,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
1,Stella,Rosenhain,1992-03-02
4,Miller,Dylan,1954-01-05

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:

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'myIdentity.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 '3,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '1,Stella,Rosenhain,1992-03-02';
Add-Content -Path $bcpFile -Value '4,Miller,Dylan,1954-01-05';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

Minta nem XML formátumú fájlra

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. További információ: Nem XML formátumú fájlok használata (SQL Server).

Az alábbi parancs a bcp segédprogram használatával hoz létre egy nem xml formátumú fájlt, myIdentity.fmta myIdentitysémája alapján.

• Ha bcp parancsot szeretne használni egy formátumfájl létrehozásához, adja meg a format argumentumot, és adatfájl elérési útja helyett használja a nul.
• 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.myIdentity format nul -c -f D:\BCP\myIdentity.fmt -t, -T

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

Fontos

Győződjön meg arról, hogy a nem XML formátumú fájl kocsivissza és sorvég jellel 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

Példák

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

A bcp használata és az identitásértékek megőrzése formátumfájl nélkül

A -E kapcsoló.

A parancssorban adja meg a következő parancsot:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t, -E

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

A bcp használata és az identitásértékek megőrzése nem XML formátumú fájllal

A kapcsolók -E és -f.

A parancssorban adja meg a következő parancsot:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T -E

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

Bcp és létrehozott identitásértékek használata formátumfájl nélkül

Alapértelmezett beállítások használata.

A parancssorban adja meg a következő parancsot:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t,

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

A bcp és a létrehozott identitásértékek használata nem XML formátumú fájllal

Használja az alapértelmezett beállításokat, és -f váltson.

A parancssorban adja meg a következő parancsot:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

A BULK INSERT használata és az identitásértékek megőrzése formátumfájl nélkül

Az KEEPIDENTITY argumentum.

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
    FROM 'D:\BCP\myIdentity.bcp'
    WITH (
        DATAFILETYPE = 'char',  
        FIELDTERMINATOR = ',',  
        KEEPIDENTITY
        );

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

A BULK INSERT használata és az identitásértékek megőrzése nem XML formátumú fájllal

Az KEEPIDENTITY és az FORMATFILE argumentumok.

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myIdentity.fmt',
        KEEPIDENTITY
        );

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

A BULK INSERT és a generált identitásértékek használata formátumfájl nélkül

Alapértelmezett beállítások használata.

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
      );

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

A BULK INSERT és a generált identitásértékek használata nem XML formátumú fájllal

Alapértelmezett értékek és FORMATFILE argumentumok használata.

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myIdentity.fmt'
        );

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

AZ OPENROWSET BULK használata és az identitásértékek megőrzése nem XML formátumú fájllal

A KEEPIDENTITY táblázat tippje és FORMATFILE argumentuma.

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
WITH (KEEPIDENTITY) 
(PersonID, FirstName, LastName, BirthDate)
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

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

OPENROWSET BULK és létrehozott identitásértékek használata nem XML formátumú fájllal

Az alapértelmezett értékek és az FORMATFILE argumentum használata.

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
(FirstName, LastName, BirthDate)
    SELECT FirstName, LastName, BirthDate
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

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

Kapcsolódó tevékenységek

• Null értékek vagy alapértelmezett értékek megőrzése tömeges importáláskor (SQL Server)

• Adatok előkészítése tömeges exportálásra vagy importálásra

Formátumfájl használata

• Formátumfájl létrehozása bcp (SQL Server)

• Használjon formátumfájlt az adatok tömeges importálásához (SQL Server)

• Táblázatoszlopok adatfájlmezőkbe (SQL Server) való leképezése formátumfájllal

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

• Táblaoszlop (SQL Server) kihagyása formátumfájllal

Adatformátumok használata tömeges importáláshoz vagy tömeges exportáláshoz

• Natív és karakterformátumú adatok importálása az SQL Server korábbi verzióiból

• Karakterformátum használata adatok importálásához vagy exportálásához (SQL Server)

• Natív formátum használata adatok importálásához vagy exportálásához (SQL Server)

• Adatok importálása vagy exportálása Unicode karakterformátummal (SQL Server)

• Unicode natív formátum használata adatok importálásához vagy exportálásához (SQL Server)

Adatformátumok megadása a kompatibilitás érdekében, amikor a bcp-t használja

1. Adja meg a mező- és sorválasztókat (SQL Server)

2. Az előtag hosszának megadása adatfájlokban a bcp (SQL Server) használatával

3. Fájltároló típusának megadása a bcp (SQL Server) használatával

Kapcsolódó tartalom

• BIZTONSÁGI MENTÉS (Transact-SQL)
• bcp segédprogram
• Tömeges beszúrás (Transact-SQL)
• OPENROWSET (Transact-SQL)
• táblamutatók (Transact-SQL)
• Fájlok formázása adatok importálásához vagy exportálásához (SQL Server)