Membuat file format dengan bcp (SQL Server)

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

Artikel ini menjelaskan cara menggunakan utilitas bcp untuk membuat file format untuk tabel tertentu. File format didasarkan pada opsi jenis data yang ditentukan (-n, , -c, -watau -N) dan pemisah tabel atau tampilan.

Saat Anda mengimpor secara massal ke tabel SQL Server atau mengekspor data secara massal dari tabel, Anda dapat menggunakan file format sebagai sistem fleksibel untuk menulis file data. File format memerlukan sedikit atau tidak ada pengeditan untuk mematuhi format data lain, atau membaca file data dari program perangkat lunak lain.

Batasan

Versi utilitas bcp (bcp.exe) yang digunakan untuk membaca file format harus sama dengan, atau lebih baru dari versi yang digunakan untuk membuat file format. Misalnya, SQL Server 2016 (13.x) bcp dapat membaca file format versi 12.0, yang dihasilkan oleh SQL Server 2014 (12.x) bcp, tetapi SQL Server 2014 (12.x) bcp tidak dapat membaca file format versi 13.0, yang dihasilkan oleh SQL Server 2016 (13.x) bcp.

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.

Membuat file format

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.

Umumnya, file format XML dan non-XML dapat dipertukarkan. Namun, kami sarankan Anda menggunakan sintaks XML untuk file format, karena memberikan beberapa keuntungan daripada file format non-XML.

File format XML

Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas. Adventure Works Cycles adalah perusahaan manufaktur fiksi yang digunakan untuk menunjukkan konsep dan skenario database.

Membuat file format XML

Untuk menggunakan perintah bcp untuk membuat file format, tentukan format argumen dan gunakan nul alih-alih jalur file data. Opsi format selalu memerlukan -f opsi , dan untuk membuat file format XML, Anda juga harus menentukan -x opsi , seperti bcp <table_or_view> format nul -f <format_file_name> -x.

Untuk membedakan file format XML, kami sarankan Anda menggunakan .xml sebagai ekstensi nama file, misalnya, MyTable.xml.

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

Contoh

Bagian ini berisi contoh berikut yang menunjukkan cara menggunakan perintah bcp untuk membuat file format XML. Tabel HumanResources.Department berisi empat kolom: DepartmentID, , NameGroupName, dan ModifiedDate.

J. Membuat file format XML untuk data karakter

Contoh berikut membuat file format XML, Department.xml, untuk HumanResources.Department tabel. File format menggunakan format data karakter dan terminator bidang non-default (,). Konten file format yang dihasilkan disajikan setelah perintah.

Perintah bcp berisi kualifikasi berikut.

Kualifikasi	Deskripsi
format nul -x -f <format_file>	Menentukan file format XML.
-c	Menentukan data karakter.
-t,	Menentukan koma (,) sebagai terminator bidang. Catatan: Jika file data menggunakan terminator bidang default (\t), -t sakelar tidak perlu.
-T	Menentukan bahwa utilitas bcp tersambung ke SQL Server dengan koneksi tepercaya menggunakan keamanan terintegrasi. Jika -T tidak ditentukan, Anda harus menentukan -U dan -P berhasil masuk.

Pada prompt perintah Windows, masukkan perintah berikut:bcp

bcp AdventureWorks2022.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T

File format yang dihasilkan, Department-c.xml, berisi elemen XML berikut:

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Untuk informasi tentang sintaks file format ini, lihat File Format XML (SQL Server). Untuk informasi tentang data karakter, lihat Menggunakan format karakter untuk mengimpor atau mengekspor data (SQL Server).

B. Membuat file format XML untuk data asli

Contoh berikut membuat file format XML, Department-n.xml, untuk HumanResources.Department tabel. File format menggunakan jenis data asli. Konten file format yang dihasilkan disajikan setelah perintah.

Perintah bcp berisi kualifikasi berikut.

Kualifikasi	Deskripsi
format nul -x -f <format_file>	Menentukan file format XML.
-n	Menentukan jenis data asli.
-T	Menentukan bahwa utilitas bcp tersambung ke SQL Server dengan koneksi tepercaya menggunakan keamanan terintegrasi. Jika -T tidak ditentukan, Anda harus menentukan -U dan -P berhasil masuk.

Pada prompt perintah Windows, masukkan perintah berikut:bcp

bcp AdventureWorks2022.HumanResources.Department format nul -x -f Department-n.xml -n -T

File format yang dihasilkan, Department-n.xml, berisi elemen XML berikut:

<?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="NativeFixed" LENGTH="2"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NativeFixed" LENGTH="8"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Untuk informasi tentang sintaks file format ini, lihat File Format XML (SQL Server). Untuk informasi tentang cara menggunakan data asli, lihat Menggunakan format asli untuk mengimpor atau mengekspor data (SQL Server).

File format non-XML

Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas. Adventure Works Cycles adalah perusahaan manufaktur fiksi yang digunakan untuk menunjukkan konsep dan skenario database.

Membuat file format non-XML

Untuk menggunakan perintah bcp untuk membuat file format, tentukan format argumen dan gunakan nul alih-alih jalur file data. Opsi ini format juga memerlukan -f opsi , seperti: bcp <table_or_view> format nul -f <format_file_name>.

Untuk membedakan file format non-XML, kami sarankan Anda menggunakan .fmt sebagai ekstensi nama file, misalnya, MyTable.fmt.

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

Contoh

Bagian ini berisi contoh berikut yang menunjukkan cara menggunakan perintah bcp untuk membuat file format non-XML. Tabel HumanResources.Department berisi empat kolom: DepartmentID, , NameGroupName, dan ModifiedDate.

J. Membuat file format non-XML untuk data asli

Contoh berikut membuat file format XML, Department-n.xml, untuk HumanResources.Department tabel. File format menggunakan jenis data asli. Konten file format yang dihasilkan disajikan setelah perintah.

Perintah bcp berisi kualifikasi berikut.

Kualifikasi	Deskripsi
format nul -f <format_file>	Menentukan file format non-XML.
-n	Menentukan jenis data asli.
-T	Menentukan bahwa utilitas bcp tersambung ke SQL Server dengan koneksi tepercaya menggunakan keamanan terintegrasi. Jika -T tidak ditentukan, Anda harus menentukan -U dan -P berhasil masuk.

Pada prompt perintah Windows, masukkan perintah berikut:bcp

bcp AdventureWorks2022.HumanResources.Department format nul -T -n -f Department-n.fmt

File format yang dihasilkan, Department-n.fmt, berisi informasi berikut:

12.0
4
1  SQLSMALLINT   0       2       ""   1     DepartmentID         ""
2  SQLNCHAR      2       100     ""   2     Name                 SQL_Latin1_General_CP1_CI_AS
3  SQLNCHAR      2       100     ""   3     GroupName            SQL_Latin1_General_CP1_CI_AS
4  SQLDATETIME   0       8       ""   4     ModifiedDate         ""

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

B. Membuat file format non-XML untuk data karakter

Contoh berikut membuat file format XML, Department.fmt, untuk HumanResources.Department tabel. File format menggunakan format data karakter dan terminator bidang non-default (,). Konten file format yang dihasilkan disajikan setelah perintah.

Perintah bcp berisi kualifikasi berikut.

Kualifikasi	Deskripsi
format nul -f <format_file>	Menentukan file format non-XML.
-c	Menentukan data karakter.
-T	Menentukan bahwa utilitas bcp tersambung ke SQL Server dengan koneksi tepercaya menggunakan keamanan terintegrasi. Jika -T tidak ditentukan, Anda harus menentukan -U dan -P berhasil masuk.

Pada prompt perintah Windows, masukkan perintah berikut:bcp

bcp AdventureWorks2022.HumanResources.Department format nul -c -f Department-c.fmt -T

File format yang dihasilkan, Department-c.fmt, berisi informasi berikut:

12.0
4
1  SQLCHAR       0       7       "\t"     1     DepartmentID            ""
2  SQLCHAR       0       100     "\t"     2     Name                    SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR       0       100     "\t"     3     GroupName               SQL_Latin1_General_CP1_CI_AS
4  SQLCHAR       0       24      "\r\n"   4     ModifiedDate            ""

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

C. Membuat file format non-XML untuk data asli Unicode

Untuk membuat file format non-XML untuk data asli Unicode untuk HumanResources.Department tabel, gunakan perintah berikut:

bcp AdventureWorks2022.HumanResources.Department format nul -T -N -f Department-n.fmt

Untuk informasi selengkapnya tentang cara menggunakan data asli Unicode, lihat Menggunakan Format Asli Unicode untuk Mengimpor atau Mengekspor Data (SQL Server).

D. Membuat file format non-XML untuk data karakter Unicode

Untuk membuat file format non-XML untuk data karakter Unicode untuk HumanResources.Department tabel yang menggunakan terminator default, gunakan perintah berikut:

bcp AdventureWorks2022.HumanResources.Department format nul -T -w -f Department-w.fmt

Untuk informasi selengkapnya tentang cara menggunakan data karakter Unicode, lihat Menggunakan format karakter Unicode untuk mengimpor atau mengekspor data (SQL Server).

F. Menggunakan file format dengan opsi halaman kode

Jika Anda membuat file format menggunakan bcp (yaitu, dengan menggunakan bcp format), informasi tentang halaman kolase/kode ditulis dalam file format.

Contoh file format berikut, untuk tabel dengan lima kolom, menyertakan kolas.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0          Cyrillic_General_CS_AS
2  SQLCHAR         0       0       "\t"             2     c_1          Cyrillic_General_CS_AS
3  SQLCHAR         0       3000    "\t"             3     c_2          Cyrillic_General_CS_AS
4  SQLCHAR         0       5       "\t"             4     c_3          ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""

Jika Anda mencoba mengimpor data ke SQL Server menggunakan bcp in -c -C65001 -f format_file ..." atau "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ...", informasi tentang halaman kolab/kode memiliki prioritas lebih dari opsi 65001.

Oleh karena itu, jika Anda membuat file format, Anda harus menghapus info kolase secara manual dari file format yang dihasilkan sebelum Anda mulai mengimpor data kembali ke SQL Server.

Contoh berikut menunjukkan file format tanpa info kolajek.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0              ""
2  SQLCHAR         0       0       "\t"             2     c_1              ""
3  SQLCHAR         0       3000    "\t"             3     c_2              ""
4  SQLCHAR         0       5       "\t"             4     c_3              ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""

Memetakan bidang data ke kolom tabel

Seperti yang dibuat oleh bcp, file format menjelaskan semua kolom tabel secara berurutan. Anda dapat mengubah file format untuk menyusun ulang atau menghilangkan baris tabel. Anda bisa mengkustomisasi file format ke file data yang bidangnya tidak dipetakan langsung ke kolom tabel. Untuk informasi lebih lanjut, baca artikel berikut:

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

Konten terkait

• Utilitas bcp
• Menggunakan file format non-XML (SQL Server)
• File Format XML (SQL Server)