Menyimpan nilai null atau default selama impor massal (SQL Server)
Berlaku untuk: SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics Analytics
Platform System (PDW)
Secara default, ketika data diimpor ke dalam tabel, perintah bcp dan pernyataan INSERT MASSAL mengamati default apa pun yang ditentukan untuk kolom dalam tabel. Misalnya, jika ada bidang null dalam file data, nilai default untuk kolom dimuat sebagai gantinya. Perintah bcp dan pernyataan BULK INSERT memungkinkan Anda menentukan bahwa nilai null dipertahankan.
Sebaliknya, pernyataan INSERT reguler mempertahankan nilai null alih-alih menyisipkan nilai default. INSERT ... PERNYATAAN SELECT * FROM OPENROWSET(BULK...) menyediakan perilaku dasar yang sama dengan INSERT reguler tetapi juga mendukung petunjuk tabel untuk menyisipkan nilai default.
Menyimpan Nilai Null
Kualifikasi berikut menentukan bahwa bidang kosong dalam file data mempertahankan nilai nullnya selama operasi impor massal, daripada mewarisi nilai default (jika ada) untuk kolom tabel. Untuk OPENROWSET, secara default, kolom apa pun yang tidak ditentukan dalam operasi beban massal diatur ke NULL.
Perintah | Pengualifikasi | Jenis kualifikasi |
---|---|---|
bcp | -k | Sakelar |
SISIPAN MASSAL | KEEPNULLS* | Argumen |
MEMASUKKAN... PILIH * DARI OPENROWSET(MASSAL...) | T/A | T/A |
* Untuk SISIPAN MASSAL, jika nilai default tidak tersedia, kolom tabel harus ditentukan untuk mengizinkan nilai null.
Catatan
Kualifikasi ini menonaktifkan pemeriksaan definisi DEFAULT pada tabel dengan perintah impor massal ini. Namun, untuk pernyataan INSERT bersamaan, definisi DEFAULT diharapkan.
Menggunakan Nilai Default dengan INSERT ... PILIH * DARI OPENROWSET(MASSAL...)
Anda dapat menentukan bahwa untuk bidang kosong dalam file data, kolom tabel terkait menggunakan nilai defaultnya (jika ada). Untuk menggunakan nilai default, gunakan petunjuk tabel KEEPDEFAULTS.
Catatan
Untuk informasi selengkapnya, lihat INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL), dan Table Hints (Transact-SQL)
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 myNulls
. Perhatikan bahwa kolom tabel keempat, Kids
, memiliki nilai default. Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
CREATE DATABASE TestDatabase;
GO
USE TestDatabase;
CREATE TABLE dbo.myNulls (
PersonID smallint not null,
FirstName varchar(25),
LastName varchar(30),
Kids varchar(13) DEFAULT 'Default Value',
BirthDate date
);
File Data Sampel
Menggunakan Notepad, buat file D:\BCP\myNulls.bcp
kosong dan sisipkan data di bawah ini. Perhatikan bahwa tidak ada nilai dalam rekaman ketiga, kolom keempat.
1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,1963-11-14
3,Stella,Rosenhain,,1992-03-02
Atau, Anda dapat menjalankan skrip PowerShell berikut untuk membuat dan mengisi file data:
cls
# revise directory as desired
$dir = 'D:\BCP\';
$bcpFile = $dir + 'MyNulls.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 '1,Anthony,Grosse,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';
#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, myNulls.fmt
, berdasarkan skema myNulls
. 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.myNulls format nul -c -f D:\BCP\myNulls.fmt -t, -T
REM Review file
Notepad D:\BCP\myNulls.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
Untuk informasi selengkapnya tentang membuat file format, lihat Membuat File Format (SQL Server).
Pertahankan Null atau Gunakan Nilai Default Selama Impor Massal
Contoh di bawah ini menggunakan file database, datafile, dan format yang dibuat di atas.
Menggunakan bcp dan Menyimpan Nilai Null tanpa Format File
-k switch. Pada prompt perintah, masukkan perintah berikut:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"
REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T -k
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"
Menggunakan bcp dan Menyimpan Nilai Null dengan File Format Non-XML
-k dan -f switches. Pada prompt perintah, masukkan perintah berikut:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"
REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T -k
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"
Menggunakan bcp dan Menggunakan Nilai Default tanpa Format File
Pada prompt perintah, masukkan perintah berikut:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"
REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"
Menggunakan bcp dan Menggunakan Nilai Default dengan File Format Non-XML
-f switch. Pada prompt perintah, masukkan perintah berikut:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"
REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"
Menggunakan INSERT MASSAL dan Menyimpan Nilai Null tanpa File Format
Argumen KEEPNULLS . Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
FROM 'D:\BCP\myNulls.bcp'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
KEEPNULLS
);
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Menggunakan SISIPAN MASSAL dan Menyimpan Nilai Null dengan File Format Non-XML
KEEPNULLS dan argumen FORMATFILE . Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
FROM 'D:\BCP\myNulls.bcp'
WITH (
FORMATFILE = 'D:\BCP\myNulls.fmt',
KEEPNULLS
);
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Menggunakan INSERT MASSAL dan Menggunakan Nilai Default tanpa Format File
Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
FROM 'D:\BCP\myNulls.bcp'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
);
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Menggunakan INSERT MASSAL dan Menggunakan Nilai Default dengan File Format Non-XML
Argumen FORMATFILE . Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
FROM 'D:\BCP\myNulls.bcp'
WITH (
FORMATFILE = 'D:\BCP\myNulls.fmt'
);
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Menggunakan OPENROWSET(BULK...) dan Menyimpan Nilai Null dengan File Format Non-XML
Argumen FORMATFILE . Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
INSERT INTO dbo.myNulls
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myNulls.bcp',
FORMATFILE = 'D:\BCP\myNulls.fmt'
) AS t1;
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Menggunakan OPENROWSET(BULK...) dan Menggunakan Nilai Default dengan File Format Non-XML
Petunjuk tabel KEEPDEFAULTS dan argumen FORMATFILE . Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
INSERT INTO dbo.myNulls
WITH (KEEPDEFAULTS)
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myNulls.bcp',
FORMATFILE = 'D:\BCP\myNulls.fmt'
) AS t1;
-- review results
SELECT * FROM TestDatabase.dbo.myNulls;
Tugas Terkait
Untuk menggunakan file format
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
Tentukan Panjang Awalan dalam File Data dengan Menggunakan bcp (SQL Server)
Tentukan Jenis Penyimpanan File dengan Menggunakan bcp (SQL Server)
Lihat Juga
BACKUP (Transact-SQL)
OPENROWSET (T-SQL)
Utilitas bcp
SISIPAN MASSAL (Transact-SQL)
Petunjuk Tabel (Transact-SQL)