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

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

File data dapat berisi bidang yang disusun dalam urutan yang berbeda dari kolom terkait dalam tabel. Artikel ini menyajikan file format non-XML dan XML yang 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. Tinjau Membuat file format dengan bcp (SQL Server) untuk informasi tambahan.

File format non-XML atau XML dapat digunakan untuk mengimpor file data secara massal ke dalam tabel, menggunakan perintah utilitas bcp , pernyataan BULK INSERT , atau pernyataan MASSAL OPENROWSET . 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. 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 kondisi pengujian

Contoh file format yang dimodifikasi dalam artikel ini didasarkan pada tabel dan file data yang ditentukan nanti dalam artikel ini.

Tabel sampel

Skrip Transact-SQL berikut membuat database pengujian dan tabel bernama myRemap.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

File data contoh

Data berikut menyajikan FirstName dan LastName dalam urutan terbalik seperti yang disajikan dalam tabel myRemap. Menggunakan editor teks seperti 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 tindakan 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

Tinjau Menggunakan file format non-XML (SQL Server) untuk informasi terperinci. Perintah berikut 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 pemisah bidang, dan T digunakan untuk mengatur koneksi tepercaya dengan 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

Untuk informasi tentang terminologi, lihat Struktur File Format Non-XML. 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 urutan nilai bidang file host berurutan.
Pastikan ada pengembalian pengangkutan setelah baris format-file terakhir.

Bandingkan perubahan:

Sebelum

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

Sesudah

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 myRemap.bcp dipetakan ke kolom pertama, myRemap.. PersonID
Bidang data kedua dalam myRemap.bcp dipetakan ke kolom ketiga, myRemap.. LastName
Bidang data ketiga di myRemap.bcp dipetakan ke kolom kedua, myRemap.. FirstName
Bidang data keempat dipetakan ke kolom keempat, myRemap.bcpmyRemap.. Gender

Membuat file dalam format XML

Tinjau file format XML (SQL Server) untuk informasi terperinci. Perintah berikut 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 pemisah bidang, dan T digunakan untuk mengatur koneksi tepercaya dengan 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 format file XML

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

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

Bandingkan perubahan:

Sebelum

<?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>

Sesudah

<?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:

BIDANG 1, yang sesuai dengan KOLOM 1, dipetakan ke kolom tabel pertama, myRemap.. PersonID
BIDANG 2, yang sesuai dengan KOLOM 2, dipetakan ulang ke kolom tabel ketiga, myRemap.. LastName
BIDANG 3, yang sesuai dengan KOLOM 3, dipetakan ulang ke kolom tabel kedua, myRemap.. FirstName
BIDANG 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 berikut menggunakan file database, datafile, dan format yang dibuat sebelumnya.

Menggunakan file format bcp dan non-XML

Pada command prompt, 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 command prompt, masukkan perintah berikut.

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

Gunakan BULK INSERT dan file format non-XML

Jalankan skrip Transact-SQL berikut.

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 skrip Transact-SQL berikut.

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;

Menggunakan file format OPENROWSET dan non-XML

Jalankan skrip Transact-SQL berikut.

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;

Menggunakan file format OPENROWSET dan XML

Jalankan skrip Transact-SQL berikut.

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;

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-09-07

Bagikan melalui

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

Contoh kondisi pengujian

Tabel sampel

File data contoh

Membuat file format

Membuat file format non-XML

Mengubah file format non-XML

Membuat file dalam format XML

Mengubah format file 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

Gunakan BULK INSERT dan file format non-XML

Menggunakan file format BULK INSERT dan XML

Menggunakan file format OPENROWSET dan non-XML

Menggunakan file format OPENROWSET dan XML

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: