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

  • Artikel

Berlaku untuk: SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

Format asli direkomendasikan saat Anda mentransfer data secara massal antara beberapa instans SQL Server menggunakan file data yang tidak berisi karakter extended/double-byte character set (DBCS).

Catatan

Untuk mentransfer data secara massal antara beberapa instans SQL Server dengan menggunakan file data yang berisi karakter yang diperluas atau DBCS, Anda harus menggunakan format asli Unicode. Untuk informasi selengkapnya, lihat Menggunakan Format Asli Unicode untuk Mengimpor atau Mengekspor Data (SQL Server).

Format asli mempertahankan jenis data asli database. Format asli ditujukan untuk transfer data data berkecepatan tinggi antara tabel SQL Server. Jika Anda menggunakan file format, tabel sumber dan target tidak perlu identik. Transfer data melibatkan dua langkah:

  1. Mengekspor data secara massal dari tabel sumber ke dalam file data

  2. Mengimpor data secara massal dari file data ke dalam tabel target

Penggunaan format asli antara tabel identik menghindari konversi jenis data yang tidak perlu ke dan dari format karakter, menghemat waktu dan ruang. Namun, untuk mencapai tingkat transfer optimal, beberapa pemeriksaan dilakukan mengenai pemformatan data. Untuk mencegah masalah dengan data yang dimuat, lihat daftar pembatasan berikut.

Batasan

Agar berhasil mengimpor data dalam format asli, pastikan bahwa:

  • File data dalam format asli.

  • Tabel target harus kompatibel dengan file data (memiliki jumlah kolom, jenis data, panjang, status NULL, dan sebagainya yang benar), atau Anda harus menggunakan file format untuk memetakan setiap bidang ke kolom terkait.

    Catatan

    Jika Anda mengimpor data dari file yang tidak cocok dengan tabel target, operasi impor mungkin berhasil tetapi nilai data yang disisipkan ke dalam tabel target kemungkinan salah. Ini karena data dari file ditafsirkan dengan menggunakan format tabel target. Oleh karena itu, setiap ketidakcocokan menghasilkan penyisipan nilai yang salah. Namun, dalam keadaan apa pun, ketidakcocokan seperti itu dapat menyebabkan inkonsistensi logis atau fisik dalam database.

    Untuk informasi tentang menggunakan file format, lihat Format File untuk Mengimpor atau Mengekspor Data (SQL Server).

Impor yang berhasil tidak akan merusak tabel target.

Cara bcp menangani data dalam format asli

Bagian ini membahas pertimbangan khusus tentang bagaimana utilitas bcp mengekspor dan mengimpor data dalam format asli.

  • Data noncharacter

    Utilitas bcp menggunakan format data biner internal SQL Server untuk menulis data noncharacter dari tabel ke file data.

  • data karakter atau varchar

    Di awal setiap bidang karakter atau varchar , bcp menambahkan panjang awalan.

    Penting

    Saat mode asli digunakan, secara default, utilitas bcp mengonversi karakter dari SQL Server ke karakter OEM sebelum menyalinnya ke file data. Utilitas bcp mengonversi karakter dari file data ke karakter ANSI sebelum mengimpornya secara massal ke dalam tabel SQL Server. Selama konversi ini, data karakter yang diperluas dapat hilang. Untuk karakter yang diperluas, gunakan format asli Unicode atau tentukan halaman kode.

  • sql_variant data

    Jika data sql_variant disimpan sebagai SQLVARIANT dalam file data format asli, data mempertahankan semua karakteristiknya. Metadata yang merekam jenis data setiap nilai data disimpan bersama dengan nilai data. Metadata ini digunakan untuk membuat ulang nilai data dengan jenis data yang sama di kolom sql_variant tujuan.

    Jika jenis data kolom tujuan tidak sql_variant, setiap nilai data dikonversi ke jenis data kolom tujuan, mengikuti aturan normal konversi data implisit. Jika terjadi kesalahan selama konversi data, batch saat ini digulung balik. Nilai karakter dan varchar apa pun yang ditransfer antara kolom sql_variant mungkin memiliki masalah konversi halaman kode.

    Untuk informasi selengkapnya tentang konversi data, lihat Konversi Tipe Data (Mesin Database).

Opsi perintah untuk format asli

Anda dapat mengimpor data format asli ke dalam tabel menggunakan bcp, BULK INSERT atau INSERT ... PILIH * DARI OPENROWSET(MASSAL...). Untuk perintah bcp atau pernyataan BULK INSERT , Anda dapat menentukan format data dalam pernyataan . Untuk INSERT ... PILIH * DARI OPENROWSET(MASSAL...) pernyataan , Anda harus menentukan format data dalam file format.

Format asli didukung oleh opsi perintah berikut:

Perintah Opsi Deskripsi
bcp -n Menyebabkan utilitas bcp menggunakan jenis data asli data.*
BULK INSERT DATAFILETYPE ='native' Menggunakan jenis data asli atau luas dari data. Perhatikan bahwa DATAFILETYPE tidak diperlukan jika file format menentukan jenis data.
OPENROWSET T/A Harus menggunakan file format

*Untuk memuat data asli (-n) ke format yang kompatibel dengan versi klien SQL Server sebelumnya, gunakan sakelar -V. Untuk informasi selengkapnya, lihat Mengimpor Data Format Asli dan Karakter dari Versi SQL Server Sebelumnya.

Catatan

Atau, Anda dapat menentukan pemformatan berdasarkan per bidang dalam file format. Untuk informasi selengkapnya, lihat Memformat File untuk Mengimpor atau Mengekspor Data (SQL Server).

Contoh kondisi pengujian

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

Contoh tabel

Skrip di bawah ini membuat database pengujian, tabel bernama myNative dan mengisi tabel dengan beberapa nilai awal. Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNative ( 
   PersonID smallint NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date,
   AnnualSalary money
   );

-- Populate table
INSERT TestDatabase.dbo.myNative
VALUES 
(1, 'Anthony', 'Grosse', '1980-02-23', 65000.00),
(2, 'Alica', 'Fatnowna', '1963-11-14', 45000.00),
(3, 'Stella', 'Rossenhain', '1992-03-02', 120000.00);

-- Review Data
SELECT * FROM TestDatabase.dbo.myNative;

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. Silakan tinjau File Format Non-XML (SQL Server) untuk informasi terperinci. Perintah berikut akan menggunakan utilitas bcp untuk menghasilkan file format non-xml, myNative.fmt, berdasarkan skema myNative. 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, dan T digunakan untuk menentukan koneksi tepercaya menggunakan keamanan terintegrasi. Pada prompt perintah, masukkan perintah berikut:

bcp TestDatabase.dbo.myNative format nul -f D:\BCP\myNative.fmt -T 

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

Penting

Pastikan file format non-XML Anda berakhir dengan pengangkutan return\line feed. Jika tidak, Anda mungkin 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 database, dan memformat file yang dibuat di atas.

Menggunakan format bcp dan asli untuk mengekspor data

-n alihkan dan perintah OUT . Catatan: file data yang dibuat dalam contoh ini akan digunakan dalam semua contoh berikutnya. Pada prompt perintah, masukkan perintah berikut:

bcp TestDatabase.dbo.myNative OUT D:\BCP\myNative.bcp -T -n

REM Review results
NOTEPAD D:\BCP\myNative.bcp

Menggunakan format bcp dan asli untuk mengimpor data tanpa file format

-n switch dan perintah IN . Pada prompt perintah, masukkan perintah berikut:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNative;"

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -T -n

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNative;"

Menggunakan format bcp dan asli untuk mengimpor data dengan file format non-XML

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

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNative;"

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -f D:\BCP\myNative.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNative;"

Menggunakan INSERT MASSAL dan format asli tanpa file format

Argumen DATAFILETYPE . Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
	FROM 'D:\BCP\myNative.bcp'
	WITH (
		DATAFILETYPE = 'native'
		);

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

Menggunakan INSERT MASSAL dan format asli dengan file format non-XML

Argumen FORMATFILE . Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
   FROM 'D:\BCP\myNative.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myNative.fmt'
		);

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

Menggunakan OPENROWSET dan format asli dengan file format non-XML

Argumen FORMATFILE . Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myNative;  -- for testing
INSERT INTO TestDatabase.dbo.myNative
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myNative.bcp', 
		FORMATFILE = 'D:\BCP\myNative.fmt'  
		) AS t1;

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

Untuk menggunakan format data untuk impor massal atau ekspor massal

Lihat juga

utilitas bcp
SISIPAN MASSAL (Transact-SQL)
Tipe Data (Transact-SQL)
sql_variant (Transact-SQL)
Impor Data Format Asli dan Karakter dari Versi SQL Server Sebelumnya
OPENROWSET (Transact-SQL)
Gunakan Format Asli Unicode untuk Mengimpor atau Mengekspor Data (SQL Server)