Shulou(Shulou.com)05/31 Report--

Editor to share with you how SQL Server Bulk Insert batch data import, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

The Bulk Insert statement of SQL Server can import local or remote data files in batches, which is very fast. Remote files must be shared, and the file path must be in the form of "\\ server name or IP\ share name\ path\ file name" (UNC).

* 1. Since Bulk Insert is usually more convenient to import data in bulk with format files, this paper first introduces the method of exporting format files by the bcp tool.

Bcp is a command line utility provided by SQL Server that provides data export, import, format file export and other functions. The syntax for exporting format files is as follows:

Sql code

Bcp database name. User name。 Table name format nul-the nul must exist here and is used in cases where data is not exported and imported

The format file name of the-f output [- x]-c-x parameter specifies that the output format file is in xml format (non-xml format by default); the-c parameter specifies that the data is stored in characters and specifies'\ t'by default as the field separator; and'\ n' as the line spacer

[- t field spacer] [- r line spacing symbol]-t and-r parameters are optional to override the default spacer specified by-c

-T-- specifies that the database connection is trusted, even if you log in as Windows

* 2. Bulk Insert

Import the data file according to the format file. The syntax format is as follows:

Sql code

Bulk insert database name. User name。 Table name

From 'data file path'

With

(

Formatfile = 'format file path'

FirstRow = 2-- specifies the number of lines to start in the data file. The default is 1.

)

* 3. OPENRORWSET (BULK) function

Sometimes, using the OPENROWSET (BULK) function gives you more flexibility to select the fields you want to insert into the original table or other tables, with the syntax format as follows:

Sql code

INSERT INTO to_table_name SELECT filed_name_list

FROM OPENROWSET (BULK abnormal pathways to compressed data files, FORMATFILE=N'path_to_format_file') AS new_table_name

Of course, this function can also be used as follows:

Sql code

SELECT field_name_list INTO temp_table_name

FROM OPENROWSET (BULK abnormal pathways to compressed data files, FORMATFILE=N'path_to_format_file') AS new_table_name

Here is a complete example:

1) create the database, table and populate the test data. The script is as follows:

Sql code

-- create a database

CREATE DATABASE [db_mgr]

GO

-- create a test table

USE db_mgr

CREATE TABLE dbo.T_Student (

F_ID [int] IDENTITY (1) NOT NULL

F_Code varchar (10)

F_Name varchar (100)

F_Memo nvarchar (500)

F_Memo2 ntext

PRIMARY KEY (F_ID)

)

GO

-- populating test data

Insert Into T_Student (F_Code, F_Name, F_Memo, F_Memo2) select

'code001', 'name001',' memo001', 'remarks 001' union all select

'code002', 'name002',' memo002', 'remarks 002' union all select

'code003', 'name003',' memo003', 'remarks 003' union all select

'code004', 'name004',' memo004', 'remarks 004' union all select

'code005', 'name005',' memo005', 'remarks 005' union all select

'code006', 'name006',' memo006', 'remarks 006'

2) We can use SQL Server's master..xp_cmdshell stored procedure to transmit CMD commands to the system, so that we can directly enter bcp commands into the query processor of SQL Server without switching to command mode. SQL Server disables the stored procedure by default for security purposes. The method to enable it is as follows:

Sql code

-- Open the xp_cmdshell stored procedure (there is a security risk after opening it)

EXEC sp_configure 'show advanced options', 1

RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1

EXEC sp_configure 'show advanced options', 0

RECONFIGURE

3) use bcp to export files in format:

Sql code

EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student format nul-f C:/student_fmt.xml-x-c-T'

4) use bcp to export data files:

Sql code

EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student out C:/student.data-f C:/student_fmt.xml-T'

Truncate table db_mgr.dbo.T_Student-empty the data in the table

Note: in the process of actual use, data files can be generated by the program, such as logging and so on!

5) use Bulk Insert statement to import data files in batch:

Sql code

BULK INSERT db_mgr.dbo.T_Student

FROM 'Cvuddy student. Data'

WITH

(

FORMATFILE = 'CRAUDULAGUP student _ blank _ fmt.xml'

)

6) example of using OPENROWSET (BULK):

Sql code

INSERT INTO db_mgr.dbo.T_Student (F_Code, F_Name) SELECT F_Code, F_Name

FROM OPENROWSET (BULK Numeric Cruise Compact, FORMATFILE=N'C:/student_fmt_c.xml') AS new_table_name-T_Student table must already exist

SELECT F_Code, F_Name INTO db_mgr.dbo.tt

FROM OPENROWSET (BULK Numeric Cruise Compact, FORMATFILE=N'C:/student_fmt_c.xml') AS new_table_name-tt table may not exist

The above is all the contents of the article "how to import bulk data into SQL Server Bulk Insert". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.