SQL Server bulk insert from CSV file

In this SQL Server tutorial, you will learn how to bulk insert data from CSV files in SQL Server. I will use various examples for demonstration purposes.

• How to bulk insert in SQL Server from CSV
• SQL Server bulk insert permissions
• Bulk insert query in SQL Server from CSV file
• SQL Server bulk insert example
• SQL Server bulk insert CSV skip header
• SQL Server bulk insert CSV into new table
• SQL Server bulk insert CSV into temp table
• SQL Server bulk insert CSV identity column
• SQL Server bulk insert remove double quotes
• SQL Server bulk insert export to CSV
• SQL Server bulk insert CSV file does not exist
• SQL Server bulk insert best practices

How to bulk insert in SQL Server from CSV

Bulk insert is a technique to move a large amount of data from a source to a new destination. There are multiple ways to bulk insert data from a CSV file into a SQL server database.

You can use the SQL Server Import and Export Wizard, BCP utility, or the BULK INSERT statement. In this tutorial, we will use the BULK INSERT statement to bulk insert data from CSV.

SQL Server bulk insert permissions

In SQL Server, you need to have two types of permissions while bulk inserting data. The first is at the database level and the other at the server on which you are going to insert data.

To bulk insert data, you need to have the INSERT permission on the target table and the ADMINISTER BULK OPERATIONS on the target server. Moreover, you will need the ALTER TABLE permissions if any of the three conditions are satisfied:

• You have some constraints in your data, but you have not specified the CHECK_CONSTRAINTS options in the BULK INSERT query.
• Some triggers exist in your data, but you have not specified the FIRE_TRIGGER option during the bulk insert operation.
• You are using the KEEPIDENTITY option in order to import identity values from the data file.

Read SQL Server check user permissions on table

Bulk insert query in SQL Server from CSV file

The BULK INSERT statement in SQL server 2019, has a lot of options to deal with different kinds of files. To import a CSV file, you can use the below T-SQL query:

USE <database name>
GO
BULK INSERT <table name>
FROM <path to CSV file>
WITH (FORMAT = 'CSV'
      , FIRSTROW=2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

In the above T-SQL query, you need to specify the table name and the path of the CSV file. Also, you will add some options after the WITH keyword. For example, in the case of a CSV file, you will use the FORMAT option and set it to CSV.

Secondly, you will specify options regarding the formatting of the CSV file. For example, options related to headers, field terminator, row terminator, etc.

Now let us see an example of how you can bulk insert data from a CSV file into a table in the database.

Read How to export data from SQL Server to Excel

SQL Server bulk insert example

For this example, I have prepared a CSV file and entered some data into it. I also have created a table in the database where I will import this CSV file using the BULK INSERT query.

• The name of the CSV file is TestData.csv. In this CSV file, I have entered some random names and phone numbers of people from the USA (United States of America). The file looks like this:

• Suppose I want to import data from this CSV file to a table named dbo.Persons in the database, I will use the BULK INSERT statement as:

USE BackupDatabase
GO
BULK INSERT dbo.Persons
FROM 'C:\Users\Blades\Documents\TestData.csv'
WITH (FORMAT = 'CSV'
      , FIELDTERMINATOR = ','
      , ROWTERMINATOR = '0x0a');

• In the above code, I have specified the column terminator or field terminator and the row terminator. In the ROWTERMINATOR option, I have specified the hexadecimal value of LF i.e line break.

The message in the output shows the number of rows affected. This means the data is successfully imported into the table.

In this way, you can use the BULK INSERT statement to bulk insert data from a CSV file into a table in a SQL Server database.

Read SQL Server Convert Datetime to String

SQL Server bulk insert CSV skip header

Sometimes, your CSV file may contain headers in the first row. While importing data using the BULK INSERT statement, you need to specify the FIRSTROW option. Otherwise, you will face an error.

If your first row is a header row and your data starts from the second row, you have to specify the FIRSTROW option as 2.

For example, I will use the same CSV file that I used in the above example i.e. random names and phone numbers of the people of the USA. But this time, I will add headers in the first row.

Also, I will delete data from the SQL server table dbo.Persons that I imported in the above example and I will perform this operation again.

Now I will write the BULK INSERT statement as:

USE BackupDatabase
GO
BULK INSERT dbo.Persons
FROM 'C:\Users\Blades\Documents\DemoData.csv'
WITH (FORMAT = 'CSV'
	  , FIRSTROW = 2
      , FIELDTERMINATOR = ','
      , ROWTERMINATOR = '0x0a');

You can see that all the 15 rows from the CSV file are inserted into the dbo.Persons table.

Hence, in this way, you can skip the headers in your CSV file while importing data from this file into a table using the BULK INSERT statement in SQL Server.

Read How to execute function in SQL with parameters

SQL Server bulk insert CSV into new table

If you want to bulk insert data from a CSV file into a new table, you cannot use the BULK INSERT statement. The BULK INSERT statement works only if the destination table exists.

However, you can use the SQL Server Import and Export Wizard. You can import data from a CSV file into a new table using this Wizard. The SQL Server Import and Export Wizard will create a table and import data into it.

• In SQL Server Import and Export Wizard, you have to select the data source as Flat File Source and select the CSV file. Also, you have to specify the format of the CSV file at this step.

• In the next step, you will see the preview of the data in the CSV file. Verify the data in the CSV file and move to the next step.

• Now you have to enter the Destination details. In my case, I will select the SQL Server Native Client 11.0 as I am using Windows Authentication.
• You have to select the server and the database where you want to create a table. Also, you have to provide the login credentials for the same.

• Move to the next steps and you will see that a new table in the database will be created with the data imported from the CSV file.

Hence, in this way, you can import data from a CSV file into a new table in an SQL Server database.

Also read, SQL Server Convert String to Date + Examples

SQL Server bulk insert CSV into temp table

In this section, I will explain how you can bulk insert data from a CSV file into a temp table in SQL Server.

Suppose I have a CSV file as shown in the below image:

I will create a temp table in my SQL Server database where I will insert the data from the CSV file.

CREATE TABLE #RankCompany(
	[Rank] [int] IDENTITY(1,1) NOT NULL,
	[Company Name] [nvarchar](20) NULL,
	[Country] [nvarchar](20) NULL
) ON [PRIMARY]
GO

Now I will use the BULK INSERT statement to import data from the CSV file into this table as:

BULK INSERT #RankCompany
FROM 'C:\Users\Blades\Documents\NewData.csv'
WITH (FORMAT = 'CSV'
	  , FIRSTROW = 2
	  ,KEEPIDENTITY)

You can verify in the tempdb database that the table is created with the data that was stored in the CSV file.

In this way, you can bulk insert data into a temp table in SQL Server.

Read SQL Server Convert Datetime to date + Examples

SQL Server bulk insert CSV identity column

You may face some scenarios where you have an identity column in your table. If you try to bulk insert data into that table, new identity values will be assigned.

But, what if have the identity values stored in the CSV file and you want to store those values as it is. In such a case, you have to specify the KEEP IDENTITY option while using the BULK INSERT statement. Let us understand this with the help of an example.

Suppose there is a table in the database named dbo.RankCompany. The table has an identity column named Rank. I have deleted some rows from the table.

When I inserted data from a CSV file into this table, I did not get the same values as there were in the CSV file in the Rank column. You can see my CSV file and the data in the table after the Bulk insert operation.

In this case, I deleted all the data from the table and performed the bulk operation again. This time I specified the KEEPIDENTITY option in the BULK INSERT statement as follows:

USE BackupDatabase
GO
BULK INSERT dbo.RankCompany
FROM 'C:\Users\Blades\Documents\NewData.csv'
WITH (FORMAT = 'CSV'
	  , FIRSTROW = 2
	  ,KEEPIDENTITY)

After that, I got the identity values as it is in the table. You can see that table data in the below image:

In this way, you can import your existing identity values into a table with an identity column in SQL Server.

Read SQL Server Convert Function

SQL Server bulk insert remove double quotes

Sometimes, you may have data in a CSV file that is surrounded by double quotes i.e. every column value is enclosed between double-quotes. For example:

"value1", "value2", "value3", "value4"

If you try to import this data into a SQL Server table using SQL Server Import and Export Wizard, this data will be entered as it is i.e column values will be surrounded by double-quotes.

This is because you did not specify the Text Qualifier while importing data using SQL Server Import and Export Wizard.

You have to set the Text Qualifier as double quotes(“) while selecting the source for importing. Look at the below image for reference.

The alternative way for doing this is using the BULK INSERT statement. For example:

Suppose, I have data in this format and I want to import this data into a table named dbo.RankCompany in a SQL Server database.

I will use the BULK INSERT statement as:

USE BackupDatabase
GO
BULK INSERT dbo.RankCompany
FROM 'C:\Users\Blades\Documents\NewData.csv'
WITH (FORMAT = 'CSV'
	  , FIRSTROW = 2
	  ,KEEPIDENTITY)

After executing the above T-SQL query, my data got imported into the table without being enclosed between double-quotes.

Thus, you might have learned how to bulk insert data having column values surrounded by double quotes into a SQL Server database table.

Read SQL Server function return table

SQL Server bulk insert export to CSV

In this section, I will explain how you can export data from a table in a SQL Server database to a CSV file.

You can use either the BCP utility or SQL Server Import and Export Wizard to export data to a CSV file. The recommended method is to use the SQL Server Import and Export Wizard as it is easy very convenient to use.

• In SQL Server Import and Export Wizard, select the Data source according to your requirements. I will select SQL Server Native Client 11.0 as I am using SQL Server Express edition and I have not hosted my database on a network.
• Then you have to select the authentication method and the authentication details that you use to connect to your database. Also, you have to specify to which database you want to connect.

• After that, you have to select the data destination as Flat File Destination. Here you can select various options regarding the format of your CSV file.

• In the next step, you have to select whether you want to copy whole tables or views or you want to copy the data returned by a T-SQL query. After selecting the desired option, click on Next.

• In the upcoming steps, you have to select the row delimeter and the field terminator which you can change according to your requirements, else use the default delimiters. You can also preview your data.
• After selecting the required options, an SSIS package will be created. You can save this package if you want, else just run it by clicing on Finish.

• In the next window, you can track the status of the operations. If the data is exported successfully, you will the output like shown in the below image:

Thus, you might have learned how to export data from a SQL Server table to a CSV file using SQL Server Import and Export Wizard.

Read Arithmetic operators in SQL Server

SQL Server bulk insert CSV file does not exist

While bulk inserting data from a CSV file into a SQL Server database, you might have faced the error “Cannot bulk load. The file does not exist or you don’t have file access rights.“

In that case, you can try the below steps:

• Verify the file name and the path properly.
• Check if the SQL Server user has the permissions to access the CSV file or not. You will need sufficient permissions to read the CSV file.
• If you are trying to read the file from a disk, SQL Server will read it from its local storage. Make sure that the file is in the local storage of the SQL Server instance. Otherwise, if the file is stored remotely, you need to specify the Universal Naming Convention(UNC) name.

Read SQL Server move database files

SQL Server bulk insert best practices

In this section, we will discuss the best practices for bulk inserting data into a SQL Server database. We will discuss some ways to improve the performance of a bulk insert operation.

The following are some points that you can consider while bulk inserting data to a SQL Server database:

1. You should override the default constraints if required, because sometimes doing not so can make your data inconsistent.
   • For example, if you try to bulk insert data into a table having an Idenitty column, you will find that the new table has different identity values as compared to the data file.
   • This is because, the table is assigning new identity values.
   • To mitigate this, you can use apply the KEEPIDENTITY constraint on the data, while performing a bulk insert operation. I have already demonstrated the use of KEEPIDENTITY option in the above sections.
2. Bulk insert operations can be performed inside transactions.
   • The benefit of this approach is that if a bulk insert operation is failed, the whole transaction can be rolled back and perfromed again.
   • For example, if after 500 number of records, the transaction fails due to some error, you can rollback the transaction and start the transaction again from the beginning.
3. Removing existing indexes and adding them after the bulk insert operation can increase the performnce of the operation.
   • For example, if we are inserting data into an empty table, instead of directly importing the indexes, we can drop the indexes and add them after the bulk insert query.
   • Similarly, you can analyse the peformance by comapring the size of existing data in the table and the data to be bulk inserted.
   • If the new data to be imported is comparitivley very less than the existing data, you can choose to drop the indexes and adding them after the bulk insert operation.
4. Bulk insert data in batches if the size of the data is very huge. You can use this by using the ROWS_PER_BATCH option in the BULK INSERT statement.
5. Sort data in the flat file( e.g. CSV file) and specify in the BULK INSERT statement that the data is already sorted using the ORDER option. If you have sorted the data already, SQL Server will not sort the data as it sorts using the clustered indexes.
6. Use the TABLOCK option if it is possible. This can improve the performance of the BULK INSERT operation.

You may like the following SQL server tutorials:

• How to test stored procedure in SQL Server
• SQL Operand data type real is invalid for modulo operator
• Could not find stored procedure in SQL Server
• Remote procedure call failed in SQL Server Configuration Manager
• Error: 40 – could not open a connection to sql server
• How to get list of users in SQL Server
• How to check if SQL Server is running

Thus, you might have learned how to bulk insert data from a CSV file into a SQL Server database and what are the best practices to do so

• How to bulk insert in SQL Server from CSV
• SQL Server bulk insert permissions
• Bulk insert query in SQL Server from CSV file
• SQL Server bulk insert example
• SQL Server bulk insert CSV skip header
• SQL Server bulk insert CSV into new table
• SQL Server bulk insert CSV into temp table
• SQL Server bulk insert CSV identity column
• SQL Server bulk insert remove double quotes
• SQL Server bulk insert export to CSV
• SQL Server bulk insert CSV file does not exist
• SQL Server bulk insert best practices