Menyimpan nilai null atau default selama impor massal (SQL Server)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 reguler INSERT mempertahankan nilai null alih-alih menyisipkan nilai default. INSERT ... SELECT * FROM OPENROWSET BULK statement menyediakan perilaku dasar yang sama seperti INSERT reguler tetapi juga mendukung petunjuk tabel untuk menyisipkan nilai default.

Pertahankan 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 BULK, secara default, kolom apa pun yang tidak ditentukan dalam operasi beban massal diatur ke NULL.

Perintah	Pengualifikasi	Jenis kualifikasi
bcp	-k	Sakelar
BULK INSERT	KEEPNULLS*	Argumen
INSERT ... SELECT * FROM OPENROWSET(BULK...)	Tidak Berlaku	Tidak Berlaku

* Untuk BULK INSERT (Transact-SQL), jika nilai default tidak tersedia, kolom tabel harus didefinisikan untuk mengizinkan nilai null.

Catatan

Kualifikasi ini menonaktifkan pemeriksaan definisi DEFAULT pada tabel dengan perintah impor massal ini. Namun, untuk pernyataan INSERT yang bersamaan, diharapkan ada DEFAULT definisi.

Gunakan Nilai Default dengan INSERT ... SELECT * DARI OPENROWSET BULK

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.

Untuk informasi selengkapnya, lihat OPENROWSET BULK.

Contoh kondisi pengujian

Contoh menggunakan database dan format file yang dibuat dalam artikel ini.

Ubah lokasi file lokal sampel kode ke lokasi file di komputer Anda.

Tabel Sampel

Skrip membuat database pengujian dan tabel bernama myNulls. 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 contoh

Menggunakan Notepad, buat file D:\BCP\myNulls.bcp kosong dan sisipkan data sampel berikut. Tidak ada nilai pada kolom keempat dari rekaman ketiga.

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. Untuk informasi selengkapnya, lihat Menggunakan file format Non-XML (SQL Server).

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.
• c digunakan untuk menentukan data karakter
• t, digunakan untuk menentukan koma sebagai pemisah bidang
• T digunakan untuk menentukan koneksi tepercaya menggunakan keamanan terintegrasi.

Pada jendela command prompt, 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 carriage return\line feed. 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 dengan bcp (SQL Server).

Pertahankan null atau gunakan nilai default selama impor massal

Contohnya menggunakan database, datafile, dan format file yang dibuat dalam artikel ini.

Gunakan bcp dan pertahankan nilai null tanpa file format

Tombolnya -k .

Pada jendela command prompt, 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;"

Gunakan bcp dan pertahankan nilai null dengan file format non-XML

Tombol -k dan -f .

Pada jendela command prompt, 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;"

Gunakan bcp dan nilai default tanpa menggunakan file format

Pada jendela command prompt, 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;"

Gunakan bcp dan nilai default dengan berkas format non-XML

Tombolnya -f .

Pada jendela command prompt, 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;"

Gunakan INSERT MASSAL dan pertahankan 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;

Gunakan SISIPAN MASSAL dan pertahankan nilai null dengan file format non-XML

Argumen KEEPNULLS dan 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;

Gunakan BULK INSERT dan gunakan nilai default tanpa file format

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;

Gunakan BULK INSERT dan 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;

Gunakan OPENROWSET BULK dan pertahankan 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;

Gunakan OPENROWSET BULK dan pertahankan 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

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

• Menyiapkan data untuk ekspor atau impor massal

Untuk menggunakan file format

• Membuat file format dengan bcp (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

• Mengimpor data format asli dan karakter dari versi SQL Server yang lebih lama

• Menggunakan format karakter untuk mengimpor atau mengekspor data (SQL Server)

• Menggunakan format asli untuk mengimpor atau mengekspor data (SQL Server)

• Gunakan 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 terminator kolom dan baris (SQL Server)

• Tentukan panjang awalan dalam file data menggunakan bcp (SQL Server)

• Tentukan jenis penyimpanan file menggunakan bcp (SQL Server)

Konten terkait

• BACKUP (Transact-SQL)
• OPENROWSET (T-SQL)
• OPENROWSET MASSAL (Transact-SQL)
• Utilitas BCP
• SISIPAN MASSAL (Transact-SQL)
• Petunjuk tabel (Transact-SQL)