Menggunakan file format untuk mengimpor data secara massal (SQL Server)
Berlaku untuk: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Artikel ini mengilustrasikan penggunaan file format dalam operasi impor massal. File format memetakan bidang file data ke kolom tabel. Tinjau Membuat File Format (SQL Server) untuk informasi tambahan.
Sebelum Anda mulai
- Agar file format berfungsi dengan file data karakter Unicode, semua bidang input harus berupa string teks Unicode (yaitu, string Unicode berukuran tetap atau karakter dihentikan).
- Untuk mengekspor atau mengimpor data SQLXML secara massal, gunakan salah satu jenis data berikut dalam file format Anda:
- SQLCHAR atau SQLVARCHAR (data dikirim di halaman kode klien atau di halaman kode yang tersirat oleh kolase)
- SQLNCHAR atau SQLNVARCHAR (data dikirim sebagai Unicode)
- SQLBINARY atau SQLVARBIN (data dikirim tanpa konversi apa pun).
- Azure SQL Database dan Azure Synapse Analytics hanya mendukung bcp. Untuk informasi tambahan, lihat:
- 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 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 myFirstImport. Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
CREATE DATABASE TestDatabase;
GO
USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
PersonID smallint,
FirstName varchar(25),
LastName varchar(30),
BirthDate Date
);
File data sampel
Menggunakan Notepad, buat file D:\BCP\myFirstImport.bcp kosong dan sisipkan data berikut:
1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02
Atau, Anda dapat menjalankan skrip PowerShell berikut untuk membuat dan mengisi file data:
Clear-Host
# revise directory as desired
$dir = 'D:\BCP\';
$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';
# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
Write-Host "The path $dir does not exist; please create or modify the directory.";
RETURN;
};
# Clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;
# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';
# Review content
Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;
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.
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, myFirstImport.fmt, berdasarkan skema myFirstImport. Untuk menggunakan perintah bcp untuk membuat file format, tentukan argumen format dan gunakan nul alih-alih jalur file data. Opsi format juga memerlukan opsi -f . Selain itu, untuk contoh ini, 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.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T
REM Review file
Notepad D:\BCP\myFirstImport.fmt
File format non-XML Anda, D:\BCP\myFirstImport.fmt akan terlihat sebagai berikut:
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 11 "\r\n" 4 BirthDate ""
Penting
Pastikan file format non-XML Anda berakhir dengan return\line feed pengangkutan. Jika tidak, Anda kemungkinan akan menerima pesan kesalahan berikut:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file
Membuat file format XML
Tinjau File Format XML (SQL Server) untuk informasi terperinci. Perintah berikut akan menggunakan utilitas bcp untuk membuat file format xml, myFirstImport.xml, berdasarkan skema myFirstImport. Untuk menggunakan perintah bcp untuk membuat file format, tentukan argumen format dan gunakan nul alih-alih jalur file data. Opsi format selalu memerlukan opsi -f , dan untuk membuat file format XML, Anda juga harus menentukan opsi -x . Selain itu, untuk contoh ini, 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.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T
REM Review file
Notepad D:\BCP\myFirstImport.xml
File format XML Anda, D:\BCP\myFirstImport.xml akan terlihat sebagai 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="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="11"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARCHAR"/>
<COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARCHAR"/>
<COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>
Menggunakan file format untuk mengimpor data secara massal
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:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"
REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"
Menggunakan file format bcp dan XML
Pada prompt perintah, masukkan perintah berikut:
REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"
REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T
REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"
Menggunakan File Format SISIPAN MASSAL dan Non-XML
Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport
FROM 'D:\BCP\myFirstImport.bcp'
WITH (FORMATFILE = 'D:\BCP\myFirstImport.fmt');
GO
-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;
Menggunakan FILE SISIPKAN MASSAL dan Format XML
Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport
FROM 'D:\BCP\myFirstImport.bcp'
WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');
GO
-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;
Gunakan OPENROWSET(BULK...) dan File Format Non-XML
Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myFirstImport.bcp',
FORMATFILE = 'D:\BCP\myFirstImport.fmt'
) AS t1;
GO
-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;
Gunakan OPENROWSET(BULK...) dan File Format XML
Jalankan Transact-SQL berikut di Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myFirstImport.bcp',
FORMATFILE = 'D:\BCP\myFirstImport.xml'
) AS t1;
GO
-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;
Contoh lainnya
Membuat File Format (SQL Server)
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)