BULK INSERT in SQL Server

dbtut

6 years ago

With BULK INSERT you can upload text, csv data files to the database in any format. Users must have insert privileges in the database and ADMINISTER BULK OPERATIONS privileges on the server level to perform BULK INSERT .

Below you can see the sample usage of BULK INSERT.

First, we create a text file as below.

Then we create a table with the help of the following script to save this text file into the database.

CREATE TABLE [dbo].[Names](
	[Name] [varchar](250) NULL,
	[Surname] [varchar](250) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Names](

[Name] [varchar](250) NULL,

[Surname] [varchar](250) NULL

) ON [PRIMARY]

Then, with the help of the following script, we transfer the data in this text file to our table in the database.

Explanation of parameters in the script:

DATAFILETYPE is the type of data,

FIELDTERMINATOR is column separator,

ROWTERMINATOR is row separator

USE AdventureWorks2012
GO
BULK INSERT Names
      FROM 'C:\Names.txt'
  WITH 
    ( 
       DATAFILETYPE    = 'char', 
       FIELDTERMINATOR = ' ', 
       ROWTERMINATOR   = ',',
       CODEPAGE='1254'
    );