Menggunakan file format untuk memetakan kolom tabel ke bidang file data (SQL Server)

Artikel
06/02/2023

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

File data bisa berisi bidang yang disusun dalam urutan yang berbeda dari kolom terkait dalam tabel. Topik ini menyajikan file format non-XML dan XML yang telah dimodifikasi untuk mengakomodasi file data yang bidangnya disusun dalam urutan yang berbeda dari kolom tabel. File format yang dimodifikasi memetakan bidang data ke kolom tabel terkait. Harap tinjau Buat File Format (SQL Server) untuk informasi tambahan.

Catatan

Baik 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 ... PERNYATAAN SELECT * FROM OPENROWSET(BULK...). Untuk informasi selengkapnya, lihat Menggunakan File Format untuk Mengimpor Data Secara Massal (SQL Server).

Catatan

Sintaks ini, termasuk sisipan 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.

Contoh tabel

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

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myRemap
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   Gender char(1)
   );

File data sampel

Data di bawah ini menyajikan FirstName dan LastName dalam urutan terbalik seperti yang disajikan dalam tabel myRemap. Menggunakan Notepad, buat file D:\BCP\myRemap.bcp kosong dan sisipkan data berikut:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Membuat file format

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

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

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

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

Mengubah file format non-XML

Lihat Struktur File Format Non-XML untuk terminologi. Buka D:\BCP\myRemap.fmt di Notepad dan lakukan modifikasi berikut:

Susun ulang urutan baris format-file sehingga baris berada dalam urutan yang sama dengan data di myRemap.bcp.
Pastikan nilai urutan bidang file host berurutan.
Pastikan ada pengembalian pengangkutan setelah baris format-file terakhir.

Bandingkan perubahan:

Sebelumnya

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

Sesudahnya

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

File format yang dimodifikasi sekarang mencerminkan:

Bidang data pertama di dipetakan myRemap.bcp ke kolom pertama, myRemap.. PersonID
Bidang data kedua di dipetakan myRemap.bcp ke kolom ketiga, myRemap.. LastName
Bidang data ketiga di dipetakan myRemap.bcp ke kolom kedua, myRemap.. FirstName
Bidang data keempat di dipetakan myRemap.bcp ke kolom keempat, myRemap.. Gender

Membuat file format XML

Silakan tinjau File Format XML (SQL Server) untuk informasi terperinci. Perintah berikut akan menggunakan utilitas bcp untuk membuat file format xml, myRemap.xml, berdasarkan skema myRemap. 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.myRemap format nul -c -x -f D:\BCP\myRemap.xml -t, -T

Mengubah file format XML

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

Urutan di mana <elemen FIELD> dideklarasikan dalam file format adalah urutan di mana bidang tersebut muncul dalam file data, sehingga membalikkan urutan untuk <elemen FIELD> dengan atribut ID 2 dan 3.
<Pastikan nilai atribut FIELD> ID berurutan.
Urutan <elemen COLUMN> dalam <elemen ROW> menentukan urutan di mana operasi massal mengirimkannya ke target. File format XML menetapkan setiap <elemen COLUMN> nama lokal yang tidak memiliki hubungan dengan kolom dalam tabel target operasi impor massal. Urutan <elemen COLUMN> tidak bergantung pada urutan <elemen FIELD> dalam <definisi RECORD> . Setiap <elemen COLUMN> sesuai dengan <elemen FIELD> (yang ID-nya ditentukan dalam atribut SOURCE dari <elemen COLUMN> ). Dengan demikian, nilai untuk <COLUMN> SOURCE adalah satu-satunya atribut yang memerlukan revisi. Balikkan urutan untuk <atribut COLUMN> SOURCE 2 dan 3.

Bandingkan perubahan:

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="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" 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"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</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="30" 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="1" 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="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

File format yang dimodifikasi sekarang mencerminkan:

FIELD 1, yang sesuai dengan KOLOM 1, dipetakan ke kolom tabel pertama, myRemap.. PersonID
FIELD 2, yang sesuai dengan KOLOM 2, dipetakan ulang ke kolom tabel ketiga, myRemap.. LastName
FIELD 3, yang sesuai dengan KOLOM 3, dipetakan ulang ke kolom tabel kedua, myRemap.. FirstName
FIELD 4, yang sesuai dengan KOLOM 4, dipetakan ke kolom tabel keempat, myRemap.. Gender

Mengimpor data dengan file format untuk memetakan kolom tabel ke bidang file 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.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T

Menggunakan file format bcp dan XML

Pada prompt perintah, masukkan perintah berikut:

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.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 myRemap;
BULK INSERT dbo.myRemap  
   FROM 'D:\BCP\myRemap.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');  
GO

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

Menggunakan file format BULK INSERT dan XML

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

USE TestDatabase;  
GO

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

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

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

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

USE TestDatabase;
GO

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

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

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

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

USE TestDatabase;  
GO

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

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

Menggunakan file format untuk memetakan kolom tabel ke bidang file data (SQL Server)

Contoh kondisi pengujian

Contoh tabel

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 memetakan kolom tabel ke bidang file 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 memetakan kolom tabel ke bidang file data (SQL Server)

Contoh kondisi pengujian

Contoh tabel

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 memetakan kolom tabel ke bidang file 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