Pertahankan nilai identitas saat mengimpor data secara massal (SQL Server)

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

File data yang berisi nilai identitas dapat diimpor secara massal ke dalam instans Microsoft SQL Server. Secara default, nilai untuk kolom identitas dalam file data yang diimpor diabaikan dan SQL Server menetapkan nilai unik secara otomatis. Nilai unik didasarkan pada nilai benih dan kenaikan yang ditentukan selama pembuatan tabel.

Jika file data tidak berisi nilai untuk kolom pengidentifikasi dalam tabel, gunakan file format untuk menentukan bahwa kolom pengidentifikasi dalam tabel harus dilewati saat mengimpor data. Lihat Menggunakan File Format untuk Melewati Kolom Tabel (SQL Server) untuk informasi tambahan.

Kerangka

Pertahankan Nilai Identitas Contoh Kondisi Pengujian  ● Tabel Sampel  ● File Data Sampel  ● Sampel File Format Non-XML Contoh  ● Menggunakan bcp dan Menyimpan Nilai Identitas tanpa Format File  ● Menggunakan bcp dan Menyimpan Nilai Identitas dengan File Format Non-XML  ● Menggunakan bcp dan Nilai Identitas yang Dihasilkan tanpa Format File  ● Menggunakan bcp dan Nilai Identitas yang Dihasilkan dengan File Format Non-XML  ● Menggunakan INSERT MASSAL dan Menyimpan Nilai Identitas tanpa File Format  ● Menggunakan INSERT MASSAL dan Menyimpan Nilai Identitas dengan File Format Non-XML  ● Menggunakan INSERT MASSAL dan Nilai Identitas yang Dihasilkan tanpa File Format  ● Menggunakan INSERT MASSAL dan Nilai Identitas yang Dihasilkan dengan File Format Non-XML  ● Menggunakan OPENROWSET dan Menyimpan Nilai Identitas dengan File Format Non-XML  ● Menggunakan OPENROWSET dan Nilai Identitas yang Dihasilkan dengan File Format Non-XML

Pertahankan Nilai Identitas

Untuk mencegah SQL Server menetapkan nilai identitas saat mengimpor baris data secara massal ke dalam tabel, gunakan pengualifikasi perintah keep-identity yang sesuai. Saat Anda menentukan kualifikasi keep-identity, SQL Server menggunakan nilai identitas dalam file data. Kualifikasi ini adalah sebagai berikut:

Perintah	Kualifikasi keep-identity	Jenis kualifikasi
bcp	-E	Sakelar
SISIPAN MASSAL	KEEPIDENTITY	Argumen
MEMASUKKAN... PILIH * DARI OPENROWSET(MASSAL...)	KEEPIDENTITY	Petunjuk tabel

Untuk informasi selengkapnya, lihat Utilitas bcp, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), dan Table Hints (Transact-SQL).

Catatan

Untuk membuat nomor bertahap otomatis yang dapat digunakan dalam beberapa tabel atau yang dapat dipanggil dari aplikasi tanpa merujuk tabel apa pun, lihat Nomor Urutan.

Contoh Kondisi Pengujian

Contoh dalam topik ini didasarkan pada tabel, file data, dan file format yang ditentukan di bawah ini.

Tabel Sampel

Skrip di bawah ini membuat database pengujian dan tabel bernama myIdentity. Jalankan Transact-SQL berikut di 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
   );

File Data Sampel

Menggunakan Notepad, buat file D:\BCP\myIdentity.bcp kosong dan sisipkan data di bawah ini.

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

Atau, Anda dapat menjalankan skrip PowerShell berikut untuk membuat dan mengisi file data:

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;

Contoh File Format Non-XML

SQL Server mendukung dua jenis file format: format non-XML dan format XML. Format non-XML adalah format asli yang didukung oleh versi SQL Server sebelumnya. Harap tinjau File Format Non-XML (SQL Server) untuk informasi terperinci. Perintah berikut akan menggunakan utilitas bcp untuk menghasilkan file format non-xml, myIdentity.fmt, berdasarkan skema myIdentity. Untuk menggunakan perintah bcp untuk membuat file format, tentukan argumen format dan gunakan nul alih-alih jalur file data. Opsi format juga memerlukan opsi -f . Selain itu, untuk contoh ini, kualifikasi c digunakan untuk menentukan data karakter, t, digunakan untuk menentukan koma sebagai terminator bidang, dan T digunakan untuk menentukan koneksi tepercaya menggunakan keamanan terintegrasi. Pada prompt perintah, masukkan perintah berikut:

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

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

Penting

Pastikan file format non-XML Anda berakhir dengan return\line feed pengangkutan. Jika tidak, Anda kemungkinan akan menerima pesan kesalahan berikut:

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

Contoh

Contoh di bawah ini menggunakan file database, datafile, dan format yang dibuat di atas.

Menggunakan bcp dan Menyimpan Nilai Identitas tanpa Format File

-E switch. Pada prompt perintah, masukkan perintah berikut:

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

Menggunakan bcp dan Menyimpan Nilai Identitas dengan File Format Non-XML

- Sakelar E dan -f . Pada prompt perintah, masukkan perintah berikut:

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

Menggunakan bcp dan Nilai Identitas yang Dihasilkan tanpa Format File

Menggunakan default. Pada prompt perintah, masukkan perintah berikut:

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

Menggunakan bcp dan Nilai Identitas yang Dihasilkan dengan File Format Non-XML

Menggunakan default dan sakelar -f . Pada prompt perintah, masukkan perintah berikut:

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

Menggunakan INSERT MASSAL dan Menyimpan Nilai Identitas tanpa Format File

Argumen KEEPIDENTITY . Jalankan Transact-SQL berikut di 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;

Menggunakan INSERT MASSAL dan Menyimpan Nilai Identitas dengan File Format Non-XML

KEEPIDENTITY dan argumen FORMATFILE . Jalankan Transact-SQL berikut di 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;

Menggunakan INSERT MASSAL dan Nilai Identitas yang Dihasilkan tanpa Format File

Menggunakan default. Jalankan Transact-SQL berikut di 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;

Menggunakan INSERT MASSAL dan Nilai Identitas yang Dihasilkan dengan File Format Non-XML

Menggunakan default dan argumen FORMATFILE . Jalankan Transact-SQL berikut di 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;

Menggunakan OPENROWSET(BULK...) dan Menyimpan Nilai Identitas dengan File Format Non-XML

Petunjuk tabel KEEPIDENTITY dan argumen FORMATFILE . Jalankan Transact-SQL berikut di 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;

Menggunakan OPENROWSET(BULK...) dan Nilai Identitas yang Dihasilkan dengan File Format Non-XML

Menggunakan default dan argumen FORMATFILE . Jalankan Transact-SQL berikut di 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;

Tugas Terkait

• Pertahankan Null atau Gunakan Nilai Default Selama Impor Massal (SQL Server)

• Menyiapkan Data untuk Ekspor atau Impor Massal (SQL Server)

Untuk menggunakan file format

• Membuat File Format (SQL Server)

• Menggunakan File Format untuk Mengimpor Data Secara Massal (SQL Server)

• Menggunakan File Format untuk Memetakan Kolom Tabel ke Bidang File Data (SQL Server)

• Menggunakan File Format untuk Melewati Bidang Data (SQL Server)

• Menggunakan File Format untuk Melewati Kolom Tabel (SQL Server)

Untuk menggunakan format data untuk impor massal atau ekspor massal

• Impor Data Format Asli dan Karakter dari Versi SQL Server Yang Lebih Lama

• Gunakan Format Karakter untuk Mengimpor atau Mengekspor Data (SQL Server)

• Menggunakan Format Asli untuk Mengimpor atau Mengekspor Data (SQL Server)

• Menggunakan Format Karakter Unicode untuk Mengimpor atau Mengekspor Data (SQL Server)

• Menggunakan Format Asli Unicode untuk Mengimpor atau Mengekspor Data (SQL Server)

Untuk menentukan format data untuk kompatibilitas saat menggunakan bcp

1. Tentukan Terminator Bidang dan Baris (SQL Server)

2. Tentukan Panjang Awalan dalam File Data dengan Menggunakan bcp (SQL Server)

3. Tentukan Jenis Penyimpanan File dengan Menggunakan bcp (SQL Server)

Lihat Juga

BACKUP (Transact-SQL)
Utilitas bcp
SISIPAN MASSAL (Transact-SQL)
OPENROWSET (T-SQL)
Petunjuk Tabel (Transact-SQL)
Format File untuk Mengimpor atau Mengekspor Data (SQL Server)