How to Import CSV File Into SQL Server Using Bulk Insert in SQLServer?

How to Import CSV File Into SQL Server Using Bulk Insert in SQLServer?

Restore data from csv files into SQL Server

This is very common scenario that we have to import data into sql server database from csv files. In SQL Server there is an option to insert data from a csv file into database. We are going to demonstrate how we can import data from csv files into a sql server database. The constraints are we need to have data in csv files as same structure in the table structure

BULK INSERT

Imports a data file into a database table or view in a user-specified format in SQL Server 2008 R2. Use this statement to efficiently transfer data between SQL Server and heterogeneous data sources.

Arguments :

database_name

Is the database name in which the specified table or view resides. If not specified, this is the current database.

schema_name

Is the name of the table or view schema. schema_name is optional if the default schema for the user performing the bulk-import operation is schema of the specified table or view.

table_name

Is the name of the table or view to bulk import data into. Only views in which all columns refer to the same base table can be used. For more information about the restrictions for loading data into views,

‘ data_file ‘

Is the full path of the data file that contains data to import into the specified table or view. BULK INSERT can import data from a disk (including network, floppy disk, hard disk, and so on).

BATCHSIZE =batch_size

Specifies the number of rows in a batch. Each batch is copied to the server as one transaction

CHECK_CONSTRAINTS

Specifies that all constraints on the target table or view must be checked during the bulk-import operation.

Steps to Import CSV to SQL Server / Sample CSV import to SQL Server using BULK Insert

Here we are going to show how to import csv files data into a SQL Server table using bulk Insert method. First of all create a csv file with data as follows :- id, name, age, joindate (Should be same as structure of table).  We have a table with same structure of columns : id, name, age , joindate.

Step 1 : Create a table

CREATE TABLE [dbo].[Employee](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [name] [varchar](50) NULL,
      [age] [int] NULL,
      [joindate] [datetime] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
      [id] ASC
)
) ON [PRIMARY]
 

Step 2 : Create a csv files and enter the data as same as structure table

 

Step3 : Run following script to insert data from csv to table

BULK
INSERT Employee
FROM 'c:\emp.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)