Advertisements
Home > SQL > 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'
)
Advertisements
  1. Tina
    September 5, 2011 at 8:46 am

    strangly when i do this im getting a message from SQL, that file c:\..\,,,\ file does not exists. even though it does!?!

  2. September 7, 2011 at 6:12 am

    Hi Tina,
    Can u post a code snippet that you are using so that I can verufy the code.

  3. March 6, 2012 at 8:22 pm

    $author Thanks for a wonderful post. Can not wait to come back and read more on your blog. Have a great day.

  4. October 19, 2012 at 5:02 pm

    what if the rho of the file is not separated by any thing in csv file when you open it in text file?

  5. October 20, 2012 at 8:00 pm

    Hi Anupam,

    We can mention row separator and column separator while reading csv files.
    I think it must have any separator then only we can identify the columns and rows.

  6. November 6, 2012 at 9:17 pm

    Do you mind if I quote a few of your posts as long as I provide credit and sources back to your webpage?
    My blog is in the very same niche as yours and my visitors would genuinely benefit from some of the information you present here.

    Please let me know if this okay with you. Thanks!

    http://emaciatedthales.cage-rage-newspage.com

  7. Ganesh
    March 26, 2013 at 10:51 am

    I m Getting “Msg 4864, Level 16, State 1, Line 2
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ID).”…Why like this???

  8. Panneer Selvam
    April 17, 2013 at 10:48 am

    Thank you really bulk insert working good…. But my csv file contain rows like this….. 1,7/31/2013,”LastName,FirstName”,”The text with 1,2,3 or 4″ in this case if i separate the field using comma as fieldterminator then this value(“The text with 1,2,3 or 4”) separated into 3 columns but i need them as single…. is any solution for this??

  9. Walt Klos
    June 6, 2013 at 4:18 am

    Try to change the export to separate fields by ‘|’. That can be done most times.

  10. RS
    August 4, 2013 at 7:58 am

    data type datetime vs date makes a difference

  11. August 5, 2013 at 11:43 am

    Hi RS,

    Thanks for your suggestions and comments.

  12. September 17, 2014 at 2:45 am

    As you learn more and more about the platform, take notes and
    come up with ideas on how to use it better. I recommend using a simple font that
    is easy to read in a size ranging from 32 to 40. how can businesses effectively use
    this new social media platform.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: