Menggunakan File Format untuk Melewati Kolom Tabel (SQL Server)

Artikel
04/04/2023

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

Artikel ini menjelaskan cara menggunakan file format untuk melewati impor kolom tabel saat data untuk kolom yang dilewati tidak ada dalam file data sumber. File data dapat berisi lebih sedikit bidang daripada jumlah kolom dalam tabel tujuan - yaitu, Anda dapat melewati impor kolom - hanya jika setidaknya salah satu dari dua kondisi berikut ini benar dalam tabel tujuan:

Kolom yang dilewati dapat diubah ke null.
Kolom yang dilewati memiliki nilai default.

Catatan

Sintaks ini, termasuk sisipan massal, tidak didukung di Azure Synapse Analytics. Di Azure Synapse Analytics dan integrasi platform database cloud lainnya, selesaikan pergerakan data melalui pernyataan COPY di Azure Data Factory, atau dengan menggunakan pernyataan T-SQL seperti COPY INTO dan PolyBase.

Contoh tabel dan file data

Contoh dalam artikel ini mengharapkan tabel bernama myTestSkipCol di dbo bawah skema. Anda bisa membuat tabel ini dalam database sampel seperti WideWorldImporters atau AdventureWorks di database lain. Buat tabel ini sebagai berikut:

USE WideWorldImporters;  
GO  
CREATE TABLE myTestSkipCol  
   (  
   Col1 smallint,  
   Col2 nvarchar(50) NULL,  
   Col3 nvarchar(50) not NULL  
   );  
GO

Contoh dalam artikel ini juga menggunakan file data sampel, myTestSkipCol2.dat. File data ini hanya berisi dua bidang, meskipun tabel tujuan berisi tiga kolom.

1,DataForColumn3  
1,DataForColumn3  
1,DataForColumn3

Langkah-langkah dasar

Anda bisa menggunakan file format non-XML atau file format XML untuk melewati kolom tabel. Dalam kedua kasus, ada dua langkah:

Gunakan utilitas baris perintah bcp untuk membuat file format default.
Ubah file format default di editor teks.

File format yang dimodifikasi harus memetakan setiap bidang yang ada ke kolom terkait dalam tabel tujuan. Ini juga harus menunjukkan kolom tabel atau kolom mana yang akan dilewati.

Misalnya, untuk mengimpor data secara massal dari myTestSkipCol2.dat ke dalam myTestSkipCol tabel, file format harus memetakan bidang data pertama ke Col1, lewati Col2, dan petakan bidang kedua ke Col3.

Opsi #1 - Gunakan file format non-XML

Langkah #1 - Membuat file format non-XML default

Buat file format non-XML default untuk myTestSkipCol tabel sampel dengan menjalankan perintah bcp berikut pada prompt perintah:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

Penting

Anda mungkin harus menentukan nama instans server yang Anda sambungkan dengan -S argumen . Selain itu, Anda mungkin harus menentukan nama pengguna dan kata sandi dengan -U argumen dan -P . Untuk informasi selengkapnya, lihat Utilitas bcp.

Perintah sebelumnya membuat file format non-XML, myTestSkipCol_Default.fmt. File format ini disebut file format default karena merupakan formulir yang dihasilkan oleh bcp. File format default menjelaskan korespondensi satu-ke-satu antara bidang file data dan kolom tabel.

Cuplikan layar berikut menunjukkan nilai dalam contoh file format default ini.

Diagram yang merinci file format non-XML default untuk mytestskipcol.

Catatan

Untuk informasi selengkapnya tentang bidang format-file, lihat file format non-XML (SQL Server).

Langkah #2 - Mengubah file format non-XML

Untuk mengubah file format non-XML default, ada dua alternatif. Salah satu alternatif menunjukkan bahwa bidang data tidak ada dalam file data dan tidak ada data yang akan disisipkan ke dalam kolom tabel yang sesuai.

Untuk melewati kolom tabel, edit file format non-XML default dan ubah file dengan menggunakan salah satu metode alternatif berikut:

Opsi #1 - Hapus baris

Metode yang disukai untuk melompati kolom melibatkan tiga langkah berikut:

Pertama, hapus baris format-file apa pun yang menjelaskan bidang yang hilang dari file data sumber.
Kemudian, kurangi nilai "Urutan bidang file host" dari setiap baris file format yang mengikuti baris yang dihapus. Tujuannya adalah nilai "Urutan bidang file host" berurutan, 1 hingga n, yang mencerminkan posisi aktual setiap bidang data dalam file data.
Terakhir, kurangi nilai di bidang "Jumlah kolom" untuk mencerminkan jumlah bidang aktual dalam file data.

Contoh berikut didasarkan pada file format default untuk myTestSkipCol tabel. File format yang dimodifikasi ini memetakan bidang data pertama ke Col1, melompati Col2, dan memetakan bidang data kedua ke Col3. Baris untuk Col2 telah dihapus. Pemisah setelah bidang pertama juga telah diubah dari \t ke ,.

14.0  
2  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Opsi #2 - Ubah definisi baris

Atau, untuk melewati kolom tabel, Anda dapat mengubah definisi baris format-file yang sesuai dengan kolom tabel. Dalam baris format-file ini, nilai "panjang awalan," "panjang data file host," dan "urutan kolom server" harus diatur ke 0. Selain itu, bidang "terminator" dan "kolase kolom" harus diatur ke "" (yaitu, ke nilai kosong atau NULL). Nilai "nama kolom server" memerlukan string yang tidak kosong, meskipun nama kolom aktual tidak diperlukan. Bidang format yang tersisa memerlukan nilai defaultnya.

Contoh berikut juga berasal dari file format default untuk myTestSkipCol tabel.

14.0  
3  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       0       ""       0     Col2         ""  
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Contoh dengan file format non-XML

Contoh berikut didasarkan pada myTestSkipCol tabel sampel dan myTestSkipCol2.dat file data sampel yang dijelaskan sebelumnya dalam artikel ini.

Gunakan SISIPAN MASSAL

Contoh ini berfungsi dengan menggunakan salah satu file format non-XML yang dimodifikasi yang dibuat seperti yang dijelaskan di bagian sebelumnya. Dalam contoh ini, file format yang dimodifikasi diberi nama myTestSkipCol2.fmt. Untuk menggunakan BULK INSERT untuk mengimpor file data secara massal myTestSkipCol2.dat , di SQL Server Management Studio (SSMS), jalankan kode berikut. Perbarui jalur sistem file untuk lokasi file sampel di komputer Anda.

USE WideWorldImporters;  
GO  
BULK INSERT myTestSkipCol  
   FROM 'C:\myTestSkipCol2.dat'  
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');  
GO  
SELECT * FROM myTestSkipCol;  
GO

Opsi #2 - Gunakan file format XML

Langkah #1 - Membuat file format XML default

Buat file format XML default untuk myTestSkipCol tabel sampel dengan menjalankan perintah bcp berikut ini di prompt perintah:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

Penting

Perintah sebelumnya membuat file format XML, myTestSkipCol_Default.xml. File format ini disebut file format default karena merupakan formulir yang dihasilkan oleh bcp. File format default menjelaskan korespondensi satu-ke-satu antara bidang file data dan kolom tabel.

<?xml version="1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
<RECORD>  
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>  
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
</RECORD>  
<ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
</ROW>  
</BCPFORMAT>

Catatan

Untuk informasi tentang struktur file format XML, lihat File Format XML (SQL Server).

Langkah #2 - Mengubah file format XML

Berikut adalah file format XML yang dimodifikasi, myTestSkipCol2.xml, yang melewati Col2. Entri FIELD dan ROW untuk Col2 telah dihapus dan entri telah dinumber ulang. Pemisah setelah bidang pertama juga telah diubah dari \t ke ,.

<?xml version="1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
<RECORD>  
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>  
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
</RECORD>  
<ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
</ROW>  
</BCPFORMAT>

Contoh dengan file format XML

Contoh berikut didasarkan pada myTestSkipCol tabel sampel dan myTestSkipCol2.dat file data sampel yang dijelaskan sebelumnya dalam artikel ini.

Untuk mengimpor data dari myTestSkipCol2.dat ke dalam myTestSkipCol tabel, contoh menggunakan file format XML yang dimodifikasi, myTestSkipCol2.xml.

Menggunakan SISIPAN MASSAL dengan tampilan

Dengan file format XML, Anda tidak dapat melewati kolom saat mengimpor langsung ke tabel dengan menggunakan perintah bcp atau BULK INSERT pernyataan. Namun, Anda dapat mengimpor ke semua kecuali kolom terakhir tabel. Jika Anda harus melewati kolom apa pun selain kolom terakhir, Anda harus membuat tampilan tabel target yang hanya berisi kolom yang terkandung dalam file data. Kemudian, Anda dapat mengimpor data secara massal dari file tersebut ke dalam tampilan.

Contoh berikut membuat v_myTestSkipCol tampilan pada myTestSkipCol tabel. Tampilan ini melewati kolom tabel kedua, Col2. Contoh kemudian menggunakan BULK INSERT untuk mengimpor file data ke myTestSkipCol2.dat dalam tampilan ini.

Di SSMS, jalankan kode berikut. Perbarui jalur sistem file untuk lokasi file sampel di komputer Anda.

USE WideWorldImporters;  
GO

CREATE VIEW v_myTestSkipCol AS  
    SELECT Col1,Col3  
    FROM myTestSkipCol;  
GO

BULK INSERT v_myTestSkipCol  
FROM 'C:\myTestSkipCol2.dat'  
WITH (FORMATFILE='C:\myTestSkipCol2.xml');  
GO

Gunakan OPENROWSET(BULK...)

Untuk menggunakan file format XML untuk melewati kolom tabel dengan menggunakan OPENROWSET(BULK...), Anda harus menyediakan daftar eksplisit kolom dalam daftar pemilihan dan juga dalam tabel target, sebagai berikut:

INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

Contoh berikut menggunakan OPENROWSET penyedia set baris massal dan myTestSkipCol2.xml file format. Contoh mengimpor file data secara massal myTestSkipCol2.dat ke myTestSkipCol dalam tabel. Pernyataan berisi daftar kolom eksplisit dalam daftar pilih dan juga dalam tabel target, sesuai kebutuhan.

Di SSMS, jalankan kode berikut. Perbarui jalur sistem file untuk lokasi file sampel di komputer Anda.

USE WideWorldImporters;  
GO  
INSERT INTO myTestSkipCol  
  (Col1,Col3)  
    SELECT Col1,Col3  
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',  
      FORMATFILE='C:\myTestSkipCol2.Xml'  
       ) as t1 ;  
GO

Bagikan melalui

Menggunakan File Format untuk Melewati Kolom Tabel (SQL Server)

Contoh tabel dan file data

Langkah-langkah dasar

Opsi #1 - Gunakan file format non-XML

Langkah #1 - Membuat file format non-XML default

Langkah #2 - Mengubah file format non-XML

Opsi #1 - Hapus baris

Opsi #2 - Ubah definisi baris

Contoh dengan file format non-XML

Gunakan SISIPAN MASSAL

Opsi #2 - Gunakan file format XML

Langkah #1 - Membuat file format XML default

Langkah #2 - Mengubah file format XML

Contoh dengan file format XML

Menggunakan SISIPAN MASSAL dengan tampilan

Gunakan OPENROWSET(BULK...)

Langkah berikutnya

Saran dan Komentar

Sumber Daya Tambahan: