Menggunakan file format untuk melewati bidang data (SQL Server)

Artikel
04/04/2023

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

File data dapat berisi lebih banyak bidang daripada jumlah kolom dalam tabel. Topik ini menjelaskan memodifikasi file format non-XML dan XML untuk mengakomodasi file data dengan lebih banyak bidang dengan memetakan kolom tabel ke bidang data yang sesuai dan mengabaikan bidang tambahan. Silakan tinjau Buat File Format (SQL Server) untuk informasi tambahan.

Catatan

File format non-XML atau XML dapat digunakan untuk mengimpor file data secara massal ke dalam tabel dengan menggunakan perintah utilitas bcp , pernyataan BULK INSERT , atau INSERT ... SELECT * FROM OPENROWSET(BULK...) pernyataan. Untuk informasi selengkapnya, lihat Menggunakan File Format untuk Mengimpor Data Secara Massal (SQL Server).

Catatan

Sintaks ini, termasuk penyisipan massal, tidak didukung di Azure Synapse Analytics. Dalam 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 kondisi pengujian

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

Tabel sampel

Skrip di bawah ini membuat database pengujian dan tabel bernama myTestSkipField. Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

File data sampel

Buat file D:\BCP\myTestSkipField.bcp kosong dan sisipkan data berikut:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Membuat file format

Untuk mengimpor data secara massal dari myTestSkipField.bcp ke dalam myTestSkipField tabel, file format harus melakukan hal berikut:

Petakan bidang data pertama ke kolom pertama, PersonID.
Lewati bidang data kedua.
Petakan bidang data ketiga ke kolom kedua, FirstName.
Petakan bidang data keempat ke kolom ketiga, LastName.

Metode paling sederhana untuk membuat file format adalah dengan menggunakan utilitas bcp. Pertama, buat file format dasar dari tabel yang ada. Kedua, ubah file format dasar untuk mencerminkan file data aktual.

Membuat file format non-XML

Tinjau File Format Non-XML (SQL Server) untuk informasi terperinci. Perintah berikut akan menggunakan utilitas bcp untuk menghasilkan file format non-xml, myTestSkipField.fmt, berdasarkan skema myTestSkipField. Selain itu, 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Mengubah file format non-XML

Tinjau struktur file format non-XML untuk terminologi. Buka D:\BCP\myTestSkipField.fmt di Notepad dan lakukan modifikasi berikut:

Salin seluruh baris format-file untuk FirstName dan tempelkan langsung setelah FirstName di baris berikutnya.
Tingkatkan nilai urutan bidang file host satu per satu untuk baris baru dan semua baris berikutnya.
Tingkatkan jumlah nilai kolom untuk mencerminkan jumlah bidang aktual dalam file data.
Ubah urutan kolom server dari 2 ke 0 untuk baris format-file kedua.

Bandingkan perubahan yang dibuat:

Sebelumnya

13.0
3
1       SQLCHAR    0       7       ","      1     PersonID        ""
2       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

Sesudahnya

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID     ""
2       SQLCHAR    0       25      ","      0     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       50      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

File format yang dimodifikasi sekarang mencerminkan:

4 bidang data
Bidang data pertama di dipetakan myTestSkipField.bcp ke kolom pertama, myTestSkipField.. PersonID
Bidang data kedua di tidak dipetakan myTestSkipField.bcp ke kolom apa pun.
Bidang data ketiga di dipetakan myTestSkipField.bcp ke kolom kedua, myTestSkipField.. FirstName
Bidang data keempat di dipetakan myTestSkipField.bcp ke kolom ketiga, myTestSkipField.. LastName

Membuat file format XML

Tinjau File Format XML (SQL Server) untuk informasi terperinci. Perintah berikut akan menggunakan utilitas bcp untuk membuat file format xml, myTestSkipField.xml, berdasarkan skema myTestSkipField. Selain itu, 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. Kualifikasi x harus digunakan untuk menghasilkan file format berbasis XML. Pada prompt perintah, masukkan perintah berikut:

bcp TestDatabase.dbo.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

Mengubah file format XML

Tinjau sintaks skema untuk file format XML untuk terminologi. Buka D:\BCP\myTestSkipField.xml di Notepad dan lakukan modifikasi berikut:

Salin seluruh bidang kedua dan tempelkan langsung setelah bidang kedua di baris berikutnya.
Tingkatkan nilai "FIELD ID" sebesar 1 untuk FIELD baru dan untuk setiap FIELD berikutnya.
Tingkatkan nilai "COLUMN SOURCE" sebesar 1 untuk FirstName, dan LastName untuk mencerminkan pemetaan yang direvisi.

Bandingkan perubahan yang dibuat:

Sebelumnya

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

Sesudahnya

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

File format yang dimodifikasi sekarang mencerminkan:

4 bidang data
BIDANG 1 yang sesuai dengan KOLOM 1 dipetakan ke kolom tabel pertama, myTestSkipField.. PersonID
BIDANG 2 tidak sesuai dengan KOLOM apa pun dan dengan demikian, tidak dipetakan ke kolom tabel apa pun.
BIDANG 3 yang sesuai dengan KOLOM 3 dipetakan ke kolom tabel kedua, myTestSkipField.. FirstName
BIDANG 4 yang sesuai dengan KOLOM 4 dipetakan ke kolom tabel ketiga, myTestSkipField.. LastName

Mengimpor data dengan file format untuk melewati bidang data

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

Menggunakan file format bcp dan non-XML

Pada prompt perintah, masukkan perintah berikut:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

Menggunakan file format bcp dan XML

Pada prompt perintah, masukkan perintah berikut:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

Menggunakan file format BULK INSERT dan non-XML

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

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO

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

Menggunakan file format BULK INSERT dan XML

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

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO

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

Gunakan file format OPENROWSET(BULK...) dan non-XML

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

USE TestDatabase;
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

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

Gunakan OPENROWSET(BULK...) dan file format XML

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

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

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

Menggunakan file format untuk melewati bidang data (SQL Server)

Contoh kondisi pengujian

Tabel sampel

File data sampel

Membuat file format

Membuat file format non-XML

Mengubah file format non-XML

Membuat file format XML

Mengubah file format XML

Mengimpor data dengan file format untuk melewati bidang data

Menggunakan file format bcp dan non-XML

Menggunakan file format bcp dan XML

Menggunakan file format BULK INSERT dan non-XML

Menggunakan file format BULK INSERT dan XML

Gunakan file format OPENROWSET(BULK...) dan non-XML

Gunakan OPENROWSET(BULK...) dan file format XML

Langkah berikutnya

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

Menggunakan file format untuk melewati bidang data (SQL Server)

Contoh kondisi pengujian

Tabel sampel

File data sampel

Membuat file format

Membuat file format non-XML

Mengubah file format non-XML

Membuat file format XML

Mengubah file format XML

Mengimpor data dengan file format untuk melewati bidang data

Menggunakan file format bcp dan non-XML

Menggunakan file formatbcp dan XML

Menggunakan file format BULK INSERT dan non-XML

Menggunakan file formatBULK INSERT dan XML

Gunakan file format OPENROWSET(BULK...) dan non-XML

Gunakan OPENROWSET(BULK...) dan file format XML

Langkah berikutnya

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

Menggunakan file format bcp dan XML

Menggunakan file format BULK INSERT dan XML