Import Csv Into SQL Server (with Query OR Without Query Using SSMS)

Import csv into SQL server (with query OR without query using SSMS);- in This post You Will Learn How To Import csv into SQL server full Guide.

Import csv into SQL server

While creating a database, your client may need to save old data in new database and he has asked you to import his CSV file into SQL server database, or you already have some data in .csv file and needs to import it, then we can have 2 possible ways to import csv data into sql server, using BULK insert SQL query or using SQL server management studio (SSMS) GUI options, let’s take a look on each of them one by one.

Import CSV to SQL server using query

First, we will try to import above Orders.csv file into SQL server table “Orders” using query, so to simply import the above data into SQL server table, we will use below query

BULK INSERT Orders
FROM 'D:\Orders.csv'
WITH
(
    FIRSTROW = 2, -- as 1st one is header
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

After running the above query you will see output

and you can verify it in the table

Few things to note, here

In the above query, we have rights to read file from source “D:\Orders.csv”, if you don’t have proper permission, you may get error, so make sure you have proper permissions.
In the above csv file, we are notifying “Id” data also, there can be possibility, when we don’t have Primary key, and we need to auto-increment PK, we will show you how to import csv file into sql server with auto-increment id.

Import csv into SQL with auto-increment Columnd (Id)

When we have a situation in which, we have csv file, but we cannot provide PK “Id” field inside the .csv file, and need’s to auto-increment it, we can do it by providing space with comma in csv file, so above csv file will look like this

Id,Country,Price,OrderQuantity
,India,10.00,4
,Australia, 5.00,10
,Brazil, 10.00,5
,China,5.50,5
,Nepal,20.20,10

and you can import and save it in same way, like you did before, using same query

BULK INSERT Orders
FROM 'D:\Orders.csv'
WITH
(
    FIRSTROW = 2, -- as 1st one is header
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

Import CSV file into SQL server using SQL server management Studio

In this process, we will use SQL server management studio GUI to import csv file in sql server database table, as bulk copy and other bulk import options are not available on the SQL servers, then this GUI based option is good and easy for large CSV files, since it allows you to import data, step by step and more easily.

Step 1: Select database, right-click on it -> “Tasks”->Select “Import flat file”

At first, open your SQL server management studio, and select the database ( in this case “OrderDetails”) and then right-click on it, after right-clicking on it, select “Tasks”-> Select “Import flat file”

Now, once you select “Import flat file” a new dialog box, will open, click “Next”

Step 2: Browse file and give table name

Clicking “next”, will bring the new screen, using which we need to select the “csv” file to be imported, so click on “Browse”, locate the .csv file and give table name.

Note: Table name muste be unique, means that table should be new table ( not already created table )

Step 3: Preview data before saving it

Once, you will click next after selecting file, you can preview the data before saving it into table, considering above CSV file, we have can preview like below

As you can see in the above image, we can see CSV data in preview, you can click on “Next”

Step 4: Check Data-type and map it properly, to successfully import csv

Now, you need to map csv file columns with database columns type properly,

You can change data-type, as per your csv file, once done, click “Next”

Note: you need to map data-type with columns properly, otherwise you will get conversion error.

Step 5: Check details and click finish

Once you are done with data mapping, you can verify details and click “Finish”, data will be imported, with creation of table.

Click “Finish” and in the next Screen you will see, data has been imported successfully ( if there is any error, you will see error and can check error.), then click “Close”.

You can refresh the tables of database, select table, it will show all the data imported properly.

Since, we imported the file into our “OrderDetails” database in the “Order” table, the “OrderDetails” database should contain a table named “Order”. Go to Object Explorer-> Databases -> OrderDetails-> Order

Then, you can run the query

Use OrderDetails
Select * from Orders

Import csv into SQL server (with query OR without query using SSMS)