How to import bulk data into SQL Server Bulk Insert
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!
