Menggunakan file format untuk mengimpor data secara massal (SQL Server)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 dengan bcp (SQL Server) untuk informasi tambahan.

Prasyarat

• Untuk menjalankan perintah T-SQL pada instans SQL Server Anda, gunakan SQL Server Management Studio (SSMS), ekstensi MSSQL untuk Visual Studio Code, sqlcmd, atau alat kueri T-SQL favorit Anda.

• Agar file format berfungsi dengan file data karakter Unicode, semua bidang input harus berupa string teks Unicode (yaitu, string Unicode berukuran tetap atau yang diakhiri dengan karakter).

• Untuk mengekspor atau mengimpor secara massal Contoh impor dan ekspor data dokumen XML (SQL Server) secara massal , gunakan salah satu tipe 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:

  • Memuat data ke Azure Synapse Analytics
  • Memuat data dari SQL Server ke Azure Synapse Analytics (file datar)
  • Memigrasikan Data Anda

• 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 contoh

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 contoh

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 berkas 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.

Ubah lokasi file lokal sampel kode ke lokasi file di komputer Anda.

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, myFirstImport.fmt, berdasarkan skema myFirstImport.

• Untuk menggunakan perintah bcp untuk membuat file format, tentukan format argumen dan gunakan nul alih-alih jalur file data.
• Opsi format juga memerlukan opsi -f.
• c digunakan untuk menentukan data karakter
• t, digunakan untuk menentukan koma sebagai pemisah bidang
• T digunakan untuk menentukan koneksi tepercaya menggunakan keamanan terintegrasi.

Pada jendela 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 berformat non-XML Anda diakhiri dengan pengembalian dan umpan baris. 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

Buat file berformat 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 format argumen dan gunakan nul alih-alih jalur file data.
• Opsi format selalu memerlukan -f opsi .
• Untuk membuat file format XML, Anda juga harus menentukan -x opsi .
• c digunakan untuk menentukan data karakter
• t, digunakan untuk menentukan koma sebagai pemisah bidang
• T digunakan untuk menentukan koneksi tepercaya menggunakan keamanan terintegrasi.

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

Pada jendela 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 (SQL Server)

Pada jendela 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;"

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

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;

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 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 (Transact-SQL) dan Gunakan file format Non-XML (SQL Server)

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 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 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 dengan bcp (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)

Konten terkait

• Utilitas bcp
• SISIPAN MASSAL (Transact-SQL)
• OPENROWSET MASSAL (Transact-SQL)
• Menggunakan file format Non-XML (SQL Server)
• Format file XML (SQL Server)
• Memformat file untuk mengimpor atau mengekspor data (SQL Server)