SQL Server BULK INSERT

Summary: in this tutorial, you’ll learn how to use the SQL Server BULK INSERT statement to import a data file into a database table.

Introduction to the SQL Server BULK INSERT statement

The BULK INSERT statement allows you to import a data file into a table or view in SQL Server. The following shows the basic syntax of the BULK INSERT statement:

BULK INSERT table_name
FROM path_to_file
WITH options;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table in the BULK INSERT clause. Note that you can use the fully qualified table name such as database_name.schema_name.table_name.
• Second, provide the path to the file in the FROM clause.
• Third, use one or more options after the WITH keyword.

For the detail of the BULK INSERT statement, you can read it here.

参考文档来源如下

BULK INSERT (Transact-SQL) - SQL Server | Microsoft Learn

SQL Server BULK INSERT statement example

使用BULK INSERT statement加载本地CSV文件到表中.

创建HRDB

CREATE DATABASE HRDB
GO

创建表Employees 

USE HRDB
GO

CREATE TABLE Employees (
  Id int IDENTITY PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL
)
GO

加载文件 D:\data\employees.csv

Id,First name,Last name
1,John,Doe
2,Jane,Doe
3,Lily,Bush
4,SQLServer,BULK INSERT
5,ALwayson,HRDB

into the Employees table

BULK INSERT Employees
FROM 'D:\data\employees.csv'
WITH (
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n',
  FIRSTROW = 2
);

query the data from the Employees table

SELECT * FROM employees;
Id          FirstName                                          LastName
----------- -------------------------            ----------------------------
1           John                                               Doe
2           Jane                                               Doe
3           Lily                                               Bush
4           SQLServer                                          BULK INSERT
5           ALwayson                                           HRDB