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

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 3 possible ways to import csv data in database, using BULK insert SQL query or using SQL server management studio (SSMS) GUI options or using Powershell, 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.

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

and you can verify it in the table

Above query, can be to use insert bulk CSV data into SQL Server, but we have just imported 5 rows, for example.

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
)

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

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

Output:

That's it, as you can see in the above table, we were able to import CSV in sql server database table.

Reading entire CSV file using OPENROWSET(BULK)

You can also use OPENROWSET to read contents of CSV and insert it into SQL Server database, for example

UPDATE Orders 
SET Country = 
   (SELECT * FROM OPENROWSET (BULK 'D:\Orders.csv', SINGLE_BLOB) a) 

SINGLE_BLOB option will read entire content from a file as single cell and will update all rows 'Country' value to CSV file value.

Using Powershell

You can also import CSV file to SQL Server using simply powershell script, using SQLServer Module, here is the powershell script (Run Powershell in Administrator Mode to install SQL Server Module and run below scripts)

## Install module if not installed, this is a one time install.
Install-Module SqlServer

## Input SQL Server Variables and CSV path
$csvPath = "D:\Orders.csv"
$csvDelimiter = ","
$serverName = "DESKTOP-DOG5T0Q\SQLEXPRESS"
$databaseName = "OrderDetails"
$tableSchema = "dbo"
$tableName = "Orders"

## Truncate Table
Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query "TRUNCATE TABLE $tableSchema.$tableName"

## Import CSV into SQL
Import-Csv -Path $csvPath -header "Id","Country","Price","OrderQuantity" -Delimiter $csvDelimiter | Write-SqlTableData -ServerInstance $serverName -DatabaseName $databaseName -SchemaName $tableSchema -TableName $tableName -Force

Note: If you are not able to install SQL Server module and getting error 'The term 'Write-SqlTableData' is not recognized as the name of a cmdlet, function, script file, or operable program.' You need to upgrade to powershell 5.1 or above, you can download and install powershell 7.2 here.

After executing above powershell commands I was able to import CSV file into SQL Server database.

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)