Zachování hodnot identit při hromadném importu dat (SQL Server)

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instanceazure Synapse Analytics

Datové soubory obsahující hodnoty identity je možné hromadně importovat do instance Microsoft SQL Serveru.

Ve výchozím nastavení se hodnoty sloupce identity v importovaném datovém souboru ignorují a SQL Server automaticky přiřadí jedinečné hodnoty. Jedinečné hodnoty jsou založeny na počátečních a přírůstkových hodnotách zadaných během vytváření tabulky.

Pokud datový soubor neobsahuje hodnoty sloupce identifikátoru v tabulce, použijte formátový soubor k určení, že sloupec identifikátoru v tabulce by měl být při importu dat vynechán. Další informace najdete v tématu Přeskočit sloupec tabulky (SQL Server) pomocí souboru formátu.

Zachování hodnot identit

Pokud chcete zabránit SQL Serveru v přiřazování hodnot identit při hromadném importu řádků dat do tabulky, použijte příslušný kvalifikátor příkazu keep-identity. Když zadáte kvalifikátor zachování identity, SQL Server použije hodnoty identity v datovém souboru.

Tyto kvalifikátory jsou následující:

Příkaz	Kvalifikátor pro zachování identity	Typ kvalifikátoru
bcp	-E	Vypínač
BULK INSERT	KEEPIDENTITY	Důvod
INSERT ... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Nápověda k tabulce

Další informace naleznete v tématu bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET BULK (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL) a nápovědy tabulky (Transact-SQL).

Poznámka

Pokud chcete vytvořit automaticky inkrementující číslo, které lze použít ve více tabulkách nebo které lze volat z aplikací bez odkazování na libovolnou tabulku, podívejte se na pořadová čísla.

Příklady testovacích podmínek

Příklady v tomto tématu jsou založené na tabulce, datovém souboru a formátovacím souboru definovaném níže.

Ukázková tabulka

Následující skript vytvoří testovací databázi a tabulku s názvem myIdentity. V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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

Ukázkový datový soubor

Pomocí Poznámkového bloku vytvořte prázdný soubor D:\BCP\myIdentity.bcp a vložte níže uvedená data.

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

Případně můžete spustit následující skript PowerShellu pro vytvoření a naplnění datového souboru:

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;

Ukázkový soubor formátu jiného formátu než XML

SQL Server podporuje dva typy formátového souboru: formát ne-XML a formát XML. Formát jiného formátu než XML je původní formát podporovaný staršími verzemi SQL Serveru. Další informace naleznete v tématu Použití souborů formátu JINÉ NEŽ XML (SQL Server).

Následující příkaz použije nástroj bcp k vygenerování souboru jiného formátu než XML, myIdentity.fmt, na základě schématu myIdentity.

• Pokud chcete k vytvoření formátu souboru použít příkaz b cp, zadejte format argument a místo cesty k datovému souboru použijte nul.
• Možnost formátu také vyžaduje možnost -f.
• c slouží k určení znakových dat.
• t,slouží k zadání čárky jako oddělovače pole.
• T slouží k určení důvěryhodného připojení pomocí integrovaného zabezpečení.

Na příkazovém řádku zadejte následující příkaz:

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

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

Důležitý

Ujistěte se, že soubor bez formátu XML končí znakem return\line. V opačném případě se pravděpodobně zobrazí následující chybová zpráva:

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

Příklady

Příklady používají soubory databáze, datového souboru a formátu vytvořené v tomto článku.

Použití bcp a zachování hodnot identit bez formátu souboru

Přepínač -E.

Na příkazovém řádku zadejte následující příkaz:

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

Použijte nástroj bcp a zachovejte hodnoty identity s ne-XML formátovým souborem

Přepínače -E a -f.

Na příkazovém řádku zadejte následující příkaz:

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

Použití hodnot bcp a vygenerovaných identit bez formátu souboru

Používá se výchozí nastavení.

Na příkazovém řádku zadejte následující příkaz:

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

Použití bcp a vygenerovaných hodnot identity se souborem formátu mimo XML

Použijte výchozí hodnoty a -f přepínač.

Na příkazovém řádku zadejte následující příkaz:

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

Použití BULK INSERT a zachování hodnot identity bez formátu souboru

Argument KEEPIDENTITY.

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použití BULK INSERT a zachování hodnot identity se souborem jiného formátu než XML

Argumenty KEEPIDENTITY a FORMATFILE.

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použití BULK INSERT a vygenerované hodnoty identity bez formátu souboru

Používá se výchozí nastavení.

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použití BULK INSERT a vygenerované hodnoty identity se souborem formátu mimo XML

Použití výchozích hodnot a FORMATFILE argumentů

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použijte OPENROWSET BULK a zachovejte hodnoty identity s ne-XML formátovým souborem.

Nápověda KEEPIDENTITY a FORMATFILE argument tabulky.

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použití bulky OPENROWSET a vygenerovaných hodnot identity se souborem jiného formátu než XML

Použití výchozích hodnot a argumentu FORMATFILE

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Související úkoly

• zachovat hodnoty null nebo výchozí hodnoty během hromadného importu (SQL Server)

• Příprava dat pro hromadný export nebo import

Použití formátového souboru

• Vytvoření formátu souboru pomocí bcp (SQL Server)

• Použití formátu souboru k hromadnému importu dat (SQL Server)

• Mapování sloupců tabulky na pole datových souborů (SQL Server) pomocí formátového souboru

• Použití formátu souboru k přeskočení datového pole (SQL Server)

• přeskočit sloupec tabulky (SQL Server) pomocí formátového souboru

Použití datových formátů pro hromadný import nebo hromadný export

• Import nativních dat a dat formátu znaků ze starších verzí SQL Serveru

• Použití formátu znaků k importu nebo exportu dat (SQL Server)

• Použití nativního formátu k importu nebo exportu dat (SQL Server)

• Import nebo export dat (SQL Server) pomocí formátu znaků Unicode

• Použijte nativní formát Unicode pro import nebo export dat (SQL Server)

Určení formátů dat pro zajištění kompatibility při použití bcp

1. Specifikace oddělovačů polí a řádků (SQL Server)

2. Určení délky předpony v datových souborech pomocí bcp (SQL Server)

3. Určení typu úložiště souborů pomocí bcp (SQL Server)

Související obsah

• BACKUP (Transact-SQL)
• nástroj bcp
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Nápovědy pro tabulky (Transact-SQL)
• Formát souborů pro import nebo export dat (SQL Server)