Import CSV File Into SQL Server Using Bulk Insert in SQLServer

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.

Example:

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

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\emp.txt

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'
)