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 GUI options, let's take a look on each of them one by one.

So, before we proceed to look more details into importing methods, suppose, this is our .csv sample file.

sample-csv-to-import-sql-server3-min.png

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 as below

/bulk-insert-csv-sql-server-min.png

and you can verify it in the table

import-csv-to-sql-server-min.png

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

sample-csv-auto-increment-sql-server-min.png

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.

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"

sql-server-management-studio-import-csv.png

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

import-flat-file-sql-server.png

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 )

/import-flat-file-sql-server-select-file.png

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

preview-data-sql-server-csv-import.png

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, as shown in the below image

map-data-type-import-csv.png

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.

confirm-finish-sql-import-csv.png

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".

import-complete.png

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

Output:

output.png

You may also like to read:

Aggregate Functions in SQL Server (SUM, AVG, COUNT, MIN, MAX)

Common Table Expressions (CTE) in Sql server

Check database size in Sql server ( Various Ways explained)

Row constructor in SQL server (Explanation With example)

Understanding SQL server COALESCE() with example (ISNULL comparison included)