Bagikan melalui

Menggunakan format file untuk melewatkan bidang data (SQL Server)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

File data dapat berisi lebih banyak bidang daripada jumlah kolom dalam tabel. Artikel 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.

Untuk informasi selengkapnya, lihat Membuat file format dengan bcp (SQL Server).

Catatan

File format non-XML atau XML dapat digunakan untuk mengimpor file data secara massal ke dalam tabel dengan menggunakan perintah utilitas bcp, pernyataan INSERT MASSAL (Transact-SQL), atau pernyataan INSERT ... SELECT * FROM OPENROWSET BULK (Transact-SQL). 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. 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 myTestSkipField sampel dan file D:\BCP\myTestSkipField.bcpdata . Ubah lokasi file lokal dalam sampel kode ke lokasi file di komputer Anda.

Tabel sampel

Skrip 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 Menggunakan 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 pemisah 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:

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

Bandingkan perubahan yang dibuat:

Sebelum

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

Sesudah

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 dipetakan di myTestSkipField.bcp ke kolom pertama, myTestSkipField.. PersonID
  • Bidang data kedua dalam myTestSkipField.bcp tidak dipetakan ke kolom apa pun.
  • Bidang data ketiga dalam myTestSkipField.bcp dipetakan ke kolom kedua, myTestSkipField.. FirstName
  • Bidang data keempat dalam myTestSkipField.bcp dipetakan ke kolom ketiga, myTestSkipField.. LastName

Membuat file dalam 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.

  • Pengualifikasi c digunakan untuk menentukan data karakter
  • t, digunakan untuk menentukan koma sebagai pemisah bidang
  • 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 dengan format XML

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

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

Bandingkan perubahan yang dibuat:

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="\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>

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="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
  • FIELD 1 yang sesuai dengan COLUMN 1 dipetakan ke kolom tabel pertama, myTestSkipField.. PersonID
  • FIELD 2 tidak sesuai dengan apa pun COLUMN dan dengan demikian, tidak dipetakan ke kolom tabel apa pun.
  • FIELD 3 yang sesuai dengan COLUMN 3 dipetakan ke kolom tabel kedua, myTestSkipField.. FirstName
  • FIELD 4 yang berkorespondensi dengan COLUMN 4 dipetakan ke kolom ketiga tabel, myTestSkipField.. LastName

Mengimpor data dengan file format untuk melewatkan bidang data

Contohnya menggunakan database sampel, datafile, dan file format yang dibuat dalam artikel ini.

Menggunakan bcp dan file format 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 (SQL Server)

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 (Transact-SQL) 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;

Gunakan file format BULK INSERT (Transact-SQL) dan XML (SQL Server)

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;

Menggunakan file format OPENROWSET BULK (Transact-SQL) 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 file format OPENROWSET BULK (Transact-SQL) dan XML (SQL Server)

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;

Konten terkait

  • Last updated on