Zachowaj wartości tożsamości podczas zbiorczego importowania danych (SQL Server)

Artykuł
2025-03-05

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Pliki danych zawierające wartości tożsamości można zaimportować zbiorczo do wystąpienia programu Microsoft SQL Server. Domyślnie wartości kolumny tożsamości w importowanym pliku danych są ignorowane, a program SQL Server automatycznie przypisuje unikatowe wartości. Unikatowe wartości są oparte na wartościach inicjujących i przyrostowych określonych podczas tworzenia tabeli.

Jeśli plik danych nie zawiera wartości kolumny identyfikatora w tabeli, użyj pliku formatu, aby określić, że kolumna identyfikatora w tabeli powinna zostać pominięta podczas importowania danych. Aby uzyskać dodatkowe informacje, zobacz Użyj pliku formatowania, aby pominąć kolumnę tabeli (SQL Server).

Zarys
zachować wartości tożsamości przykładowe warunki testu ● Przykładowa tabela ● przykładowy plik danych ● przykładowy plik formatu innego niż XML Przykłady ● Używanie narzędzia bcp i zachowywanie wartości tożsamości bez pliku formatu ● używanie narzędzia bcp i zachowywanie wartości tożsamości przy użyciu pliku w formacie innego niż XML ● Korzystanie z bcp i wygenerowanych wartości identity bez pliku formatu ● Używanie bcp z wykorzystaniem wygenerowanych wartości identyfikacyjnych z pliku w formacie innym niż XML ● Użycie BULK INSERT i zachowanie wartości tożsamości bez pliku formatu ● używanie funkcji BULK INSERT i utrzymywanie wartości tożsamości przy użyciu pliku w formacie innego niż XML ● Używanie BULK INSERT i wygenerowanych wartości identyfikatorów bez pliku formatu ● Przy użyciu funkcji BULK INSERT i wygenerowanych wartości tożsamości z pliku w formacie nie-XML ● używanie biblioteki OPENROWSET i utrzymywanie wartości tożsamości przy użyciu pliku w formacie innego niż XML ● przy użyciu biblioteki OPENROWSET i wygenerowanych wartości tożsamości z plikiem formatu innego niż XML

Zarys

Zachowaj wartości tożsamości

Aby uniemożliwić programowi SQL Server przypisywanie wartości tożsamości podczas zbiorczego importowania wierszy danych do tabeli, użyj odpowiedniego kwalifikatora poleceń keep-identity. Po określeniu kwalifikatora zachowania tożsamości program SQL Server używa wartości tożsamości w pliku danych. Te kwalifikatory są następujące:

Polecenie	Kwalifikator keep-identity	Typ kwalifikatora
bcp	-E	Przełącznik
WSTAWIANIE ZBIORCZE	KEEPIDENTITY	Argument
WSTAWIANIE... SELECT * FROM OPENROWSET(BULK...)	ZachowajTożsamość	Podpowiedź dla tabeli

Aby uzyskać więcej informacji, zobacz bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL)i wskazówki dotyczące tabel (Transact-SQL).

Uwaga

Aby utworzyć automatycznie zwiększającą się liczbę, która może być używana w wielu tabelach lub może być wywoływana z poziomu aplikacji bez odwoływania się do jakiejkolwiek tabeli, zobacz Numery Sekwencyjne.

Przykładowe warunki testu

Przykłady w tym temacie są oparte na tabeli, pliku danych i pliku formatu zdefiniowanym poniżej.

Przykładowej tabeli

Poniższy skrypt tworzy testową bazę danych i tabelę o nazwie myIdentity. Wykonaj następujące polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (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
   );

przykładowy plik danych

Za pomocą Notatnika utwórz pusty plik D:\BCP\myIdentity.bcp i wstaw poniższe dane.

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

Alternatywnie możesz wykonać następujący skrypt programu PowerShell, aby utworzyć i wypełnić plik danych:

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;

przykładowy plik formatu innego niż XML

Program SQL Server obsługuje dwa typy plików formatu: format inny niż XML i format XML. Format inny niż XML jest oryginalnym formatem obsługiwanym przez wcześniejsze wersje programu SQL Server. Aby uzyskać szczegółowe informacje, zapoznaj się z plikami w formacie innym niż XML (SQL Server) . Następujące polecenie użyje narzędzia bcp do wygenerowania pliku formatu innego niż xml, myIdentity.fmt, na podstawie schematu myIdentity. Aby użyć polecenia bcp do utworzenia pliku formatu, określ format argument i użyj nul zamiast ścieżki pliku danych. Opcja formatowania wymaga również opcji -f. Ponadto w tym przykładzie kwalifikator c służy do określenia danych znaków, t, służy do określenia przecinka jako terminatora polaoraz T służy do określenia zaufanego połączenia przy użyciu zintegrowanych zabezpieczeń. W wierszu polecenia wprowadź następujące polecenie:

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

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

Ważny

Upewnij się, że plik w formacie innym niż XML kończy się znakiem powrotu karetki\znakiem nowej linii. W przeciwnym razie prawdopodobnie zostanie wyświetlony następujący komunikat o błędzie:

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

Przykłady

W poniższych przykładach użyto bazy danych, pliku danych i plików formatowania utworzonych powyżej.

Używanie bcp i zachowywanie wartości tożsamości bez formatu

przełącznik -E. W wierszu polecenia wprowadź następujące polecenie:

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

Korzystanie z bcp i utrzymywanie wartości tożsamości przy użyciu plików w formacie innym niż XML

przełączniki -E i -f. W wierszu polecenia wprowadź następujące polecenie:

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

Korzystanie z bcp i wygenerowanych wartości tożsamości bez pliku formatu

Używanie wartości domyślnych. W wierszu polecenia wprowadź następujące polecenie:

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

używanie bcp i wygenerowanych wartości tożsamości przy użyciu pliku w formacie innym niż XML

Używanie wartości domyślnych i przełącznika -f. W wierszu polecenia wprowadź następujące polecenie:

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

używanie BULK INSERT i zachowanie wartości tożsamości bez pliku formatu

KEEPIDENTITY ARGUMENT. Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (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;

używanie BULK INSERT i utrzymywanie wartości tożsamości przy użyciu pliku w formacie innego niż XML

KEEPIDENTITY i FORMATFILE argument. Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (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;

przy użyciu BULK INSERT i wygenerowanych wartości tożsamości bez pliku formatu

Używanie wartości domyślnych. Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (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;

Stosowanie BULK INSERT i wygenerowanych wartości identyfikacyjnych przy użyciu pliku w formacie nie-XML

Używanie wartości domyślnych i argumentu FORMATFILE . Wykonaj zapytanie Transact-SQL w programie Microsoft SQL Server Management Studio (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;

używanie OPENROWSET(BULK...) i utrzymywanie wartości tożsamości przy użyciu pliku w formacie innego niż XML

KEEPIDENTITY wskazówka tabeli i argument FORMATFILE. Wykonaj następujące Transact-SQL w programie Microsoft SQL Server Management Studio (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;

Korzystanie z OPENROWSET(BULK...) oraz wygenerowanych wartości tożsamości z użyciem pliku w formacie nie-XML

Używanie wartości domyślnych i argument FORMATFILE. Wykonaj poniższy Transact-SQL w programie Microsoft SQL Server Management Studio (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;

Powiązane zadania

Aby użyć formatu pliku

tworzenie pliku formatu (SQL Server)
używanie pliku formatu do zbiorczego importowania danych (SQL Server)
mapowanie kolumn tabeli na Data-File pól (SQL Server) za pomocą pliku formatu
Użyj pliku formatu, aby pominąć pole danych (SQL Server)
użyć pliku formatu, aby pominąć kolumny tabeli (SQL Server)

Aby używać formatów danych do importowania zbiorczego lub eksportowania zbiorczego

Aby określić formaty danych pod kątem zgodności podczas korzystania z narzędzia bcp

Zobacz też

BACKUP (Transact-SQL)
narzędzie bcp
MASOWE WSTAWIANIE (Transact-SQL)
OPENROWSET (Transact-SQL)
wskazówki dotyczące tabel (Transact-SQL)
Formatowanie plików do importowania lub eksportowania danych (SQL Server)

Udostępnij za pośrednictwem