How to Import CSV File in SQL Server (2 Easy Ways)
Before we start, we need a sample CSV and a target table in SQL Server. Check it out in the
screenshot below.
This will be used to upload to SQL Server using 3 of the different ways to import CSV. So, download a
copy of the actor.csv file from the attachment. Remember where you saved it.
Meanwhile, the target table is structured the same in SQL Server.
In this tutorial, I’m using the server name MS-SQLSERVER. And the database name is CSV-MSSQL-
TEST.
Using BULK INSERT
BULK INSERT is a command in SQL Server to import data files into a database table. It can be used to
upload various file formats, including CSV. If you love a little coding, this is the way to go. Here’s the
code on how to bulk insert CSV into SQL Server:
The first command simply deletes all the records in the target table. Then, the BULK INSERT
command includes the target table and the CSV file. The location of the CSV should follow the rules
of the Universal Naming Convention (UNC). You also must tell SQL Server what file it’s dealing with.
In this case, FORMAT=CSV because the file is in CSV format. Finally, specify what row the data starts.
FIRSTROW = 2 because the first row contains the column names.
Using SQL Server Management Studio Import CSV Tools
If you don’t prefer coding, another useful tool is the Import Data from SQL Server Management
Studio (SSMS). Here’s how to import CSV to MSSQL table using SSMS.
Step 1. From the Object Explorer, Expand the Databases Folder
First, you need to go to Object Explorer and select the target database. Expand the Databases folder.
Check it out below.
Step 2. Select the Target Database
In this example, the target database is CSV-MSSQL-TEST. Right-click that database and then
select Tasks. And then, click Import Data.
The SQL Server Import and Export Wizard window will appear with a welcome screen. From here,
click Next.
Step 3. Select a Flat File Source
This part will let you pick the data source. For this, click the drop-down list and select Flat File
Source. Afterwards, click Next.
Step 4. Specify the CSV File
Now, you need to specify the file. Click Browse and specify the path of the CSV file as shown in the
screenshot. If you have downloaded it, specify the download location. Then, in the file type,
select CSV files (*.csv). And afterwards – actor.csv. Proceed further by clicking Open.
Step 5. Configure the Columns
Can’t SSMS detect the correct data types for each column? In our sample data, detection of the
correct types and sizes is off. So, we need to set it up ourselves.
Pro tip: Whenever you import data from one platform to another, it’s best to match the correct types
and sizes. Why? To avoid errors and headaches.
Let’s start by clicking Advanced. Then, you will see a list of columns. Click each and set the type and
size.
Based on the data types and sizes, refer to table below on what to set for each column.
Column Name DataType OutputColumnWidth
id N/A
four-byte signed integer [DT_I4]
lastname string [DT_STR] 20
firstname string [DT_STR] 20
middlename string [DT_STR] 20
suffix string [DT_STR] 3
Step 6. Choose the Destination (SQL Server)
Now, you need to specify the SQL Server as the target of the import process. To do that,
select Microsoft OLE DB Provider for SQL Server. Then, select the SQL Server name and enter the
necessary credentials. Select the Database name and click Next.
Step 7. Specify the Database Table and Check Column Mappings
After selecting the database, you also need to specify the table. Choose the Actors table.
Then select Edit Mappings to see if the columns from the source match the target.
Once the Column Mappings window appears, check if the columns from the source and target
match. You can either pick delete rows in the destination table or append rows. In our case, we want
to start cleaning. If you attempted to import using BULK INSERT earlier, there is data present in the
target table. And so, an error will occur. To avoid that, choose Delete rows in the destination table.
And then, click OK.
Finally, to end our setup for the target table, click Next
