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

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

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 tabulkové rady (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í funkce 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í funkce 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í funkce BULK INSERT a vygenerovaných hodnot 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í funkce BULK INSERT a vygenerovaných hodnot identity se souborem formátu jiného než 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
• hromadné vložení (Transact-SQL)
• OPENROWSET (Transact-SQL)
• tabulek (Transact-SQL)
• Formát souborů pro import nebo export dat (SQL Server)