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

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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).

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:

Aby uzyskać więcej informacji, zobacz bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET BULK (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), and Table hints (Transact-SQL).

Przykładowe warunki testu

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

Przykładowa tabela

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ć więcej informacji, zobacz Użyj plików 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 w celu utworzenia pliku formatu, określ argument format i użyj nul zamiast ścieżki pliku danych.
• Opcja formatowania wymaga również opcji -f.
• c służy do określania danych znaków
• t, służy do określania przecinka jako terminatora pola
• T służy do określania 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

Przykłady

W przykładach użyto bazy danych, pliku danych i plików formatu utworzonych w tym artykule.

Użyj narzędzia bcp i zachowaj wartości identyfikacyjne bez pliku 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;"

Użyj narzędzia bcp i zachowaj wartości Identity z plikiem formatu innego 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;"

Używanie wartości bcp i wygenerowanych 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żyj narzędzia bcp i wygenerowanych wartości tożsamości z plikiem formatu innego niż XML

Użyj wartości domyślnych i -f przełącznika.

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 funkcji BULK INSERT i zachowywanie wartości tożsamości bez pliku formatu

Argument KEEPIDENTITY.

Wykonaj następujące 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żyj funkcji BULK INSERT i zachowaj wartości tożsamości z plikiem formatu innego niż XML

Argumenty KEEPIDENTITY i FORMATFILE .

Wykonaj następujące 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;

Używanie funkcji BULK INSERT i wygenerowanych wartości tożsamości bez pliku formatu

Używanie wartości domyślnych.

Wykonaj następujące 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;

Używanie funkcji BULK INSERT i wygenerowanych wartości tożsamości z plikiem formatu innego niż XML

Używanie wartości domyślnych i FORMATFILE argumentu

Wykonaj następujące 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'
        );

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

Użyj OPENROWSET BULK i zachowaj wartości identyfikatorów korzystając z pliku w formacie nie-XML

Wskazówka KEEPIDENTITY tabeli i argument FORMATFILE.

Wykonaj następujące polecenie 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;

Użyj OPENROWSET BULK oraz wygenerowanych wartości Identity z plikiem o formacie innym niż XML

Używanie wartości domyślnych i argumentu FORMATFILE .

Wykonaj następujące polecenie 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

• Zachowaj wartości null lub wartości domyślne podczas importowania zbiorczego (SQL Server)

• Przygotowywanie danych do eksportu zbiorczego lub importowania

Aby użyć formatu pliku

• Tworzenie pliku formatu przy użyciu narzędzia bcp (SQL Server)

• Używanie pliku formatu do zbiorczego importowania danych (SQL Server)

• Użyj pliku formatu do mapowania kolumn tabeli na pola pliku danych (SQL Server)

• 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

• Importowanie danych w formacie natywnym i znakowym z wcześniejszych wersji programu SQL Server

• Importowanie lub eksportowanie danych (SQL Server) przy użyciu formatu znaków

• Importowanie lub eksportowanie danych (SQL Server) przy użyciu formatu natywnego

• używanie formatu znaków Unicode do importowania lub eksportowania danych (SQL Server)

• używanie formatu natywnego Unicode do importowania lub eksportowania danych (SQL Server)

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

1. Określanie terminatorów pól i wierszy (SQL Server)

2. Określanie długości prefiksu w plikach danych przy użyciu narzędzia bcp (SQL Server)

3. Określanie typu magazynu plików przy użyciu narzędzia bcp (SQL Server)

Treści powiązane

• KOPIA ZAPASOWA (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)