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.

Osnova

zachovat hodnoty identity ukázkové testovací podmínky  ● ukázková tabulka  ● ukázkový datový soubor  ● ukázkový soubor formátu mimo XML Příklady  ● Použití bcp a zachování hodnot identity bez formátovacího souboru  ● Použití bcp a zachování hodnot identity se souborem formátu jiného než XML  ● Použití bcp a vygenerovaných identit bez formátového souboru  ● Použití bcp a generovaných identitních hodnot s ne-XML formátovým souborem  ● Použití BULK INSERT a zachování hodnot identity bez formátovacího souboru  ● Použití funkce BULK INSERT a zachování hodnot identity s ne-XML formátovým souborem  ● Použití BULK INSERT a generovaných identitních hodnot bez souboru formátu  ● Použití BULK INSERT a vygenerovaných identitních hodnot se souborem ve formátu, který není XML  ● Použití OPENROWSET a uchování hodnot identit s ne-XML formátovým souborem  ● Použití OPENROWSET a vygenerovaných hodnot identity s ne-XML formátovým souborem

Zachovat hodnoty identity

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č
HROMADNÉ VLOŽENÍ	KEEPIDENTITY	Argument
VLOŽIT... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Nápověda k tabulce

Další informace naleznete v tématu bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (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říklad 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ého datového souboru

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 mimo 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. Podrobné informace najdete v sekci Soubory ve formátu jiném 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. Chcete-li k vytvoření souboru formátu použít příkaz bcp, zadejte argument formátu a místo cesty k datovému souboru použijte nul. Možnost formátu také vyžaduje možnost -f. V tomto příkladu se kvalifikátor c používá k určení znakových dat, kvalifikátor t, se používá k určení čárky jako ukončovače pole a kvalifikátor T se používá k určení důvěryhodného připojení s použitím 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

Následující příklady používají soubory databáze, datového souboru a formátu vytvořené výše.

použití bcp a zachování hodnot identity bez formátového 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žití bcp a zachování hodnot identit s souborem formátu ne-XML

-E a -f 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 -f D:\BCP\myIdentity.fmt -T -E

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

Použití bcp a vygenerovaných hodnot identity bez souboru s formátem

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 s nesouborovým formátem XML

Pomocí výchozích hodnot a přepínače -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

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

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

argument „udržet identitu“. 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 pomocí souborů formátu NEŽ XML

KEEPIDENTITY 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',
		KEEPIDENTITY
		);

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

použití 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í BULK INSERTu a generovaných identitních hodnot ze souboru formátu , který není ve 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
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myIdentity.fmt'
		);

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

použití OPENROWSET(BULK...) a zachování hodnot identity pomocí souboru formátu ne-XML

tabulkový hint KEEPIDENTITY a argument 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
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í OPENROWSET(BULK...) a vygenerovaných hodnot identity se formátu 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 použít výchozí hodnoty při hromadném importu (SQL Server)

• příprava dat pro hromadný export nebo import (SQL Server)

Použití formátového souboru

• Vytvoření Formátového Souboru (SQL Server)

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

• Mapování sloupců tabulky na pole Data-File (SQL Server) pomocí formátového souboru

• Přeskočení datového pole (SQL Server) pomocí formátového souboru

• 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 dat nativního formátu a formátu znaků ze starších verzí SQL Serveru

• použití formátu znaku k importu nebo exportu dat (SQL Server)

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

• Použijte formát znaků Unicode k importu nebo exportu dat (SQL Server)

• 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 terminátorů 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)

Viz také

BACKUP (Transact-SQL)
bcp Utility
hromadné vložení (Transact-SQL)
OPENROWSET (Transact-SQL)
Nápovědy k tabulce (Transact-SQL)
formát souborů pro import nebo export dat (SQL Server)