Bagikan melalui


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

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)

Langkah berikutnya