SQL Server bulk Data Export Import Bulk INSERT use

Brief introduction

The Bulk Insert command differs from the bcp command in that it is a SQL Server script statement that can import local or remote file data into a database in bulk, very quickly, and the remote file must be shared.

The file path must use the Universal Convention (UNC) name, which is the form of "\ \ server name or ip\ share name \ path \ file name."

Note that the remote thing here is relative to the database server, that is, if the data file is placed outside the database server, it needs to be shared with the database server;

Bulk insert typically imports data in batches with a format file exported by bcp

Bulk Insert Mate Format File syntax

Bulk Insert database name. User name. Table name
From ' Data file path '
With
(
formatfile = ' Format file path ',
FirstRow = 2--The number of rows starting in the specified data file, which is 1 by default
)

Where format files are generated using bcp

BCP SPC.dbo.BCC format nul-f c:\bcc_format.fmt-x-c-s "43.xxx.xxx.xxx"-u "sa"-P "*******"

Bulk Insert Import CSV file syntax

BULK Insert [database name. User name. Table name]
From ' Data file physical path '
With (
Fieldterminator= ', ',
Rowterminator= ' \ n '
)

CSV sample

Bulk Insert Bcc
From ' C:\\bcc.csv '
With (
Fieldterminator= ', ',
Rowterminator= ' \ n '
)

SQL Server bulk Data Export Import Bulk INSERT use