To follow along with this article, you will need:
- SQL Server instance.
- Sample CSV or text file.
For illustration, we have a CSV file containing 1000 records. You can download a sample file in the link below:
Step 1: Create Database
The first step is to create a database in which to import the CSV file. For our example, we will call the database.
We can a query as:
Once we have the database setup, we can proceed and insert the required data.
Import CSV File Using SQL Server Management Studio
We can import the CSV file into the database using the SSMS import wizard. Open the SQL Server management Studio and login to your server instance.
On the left-hand pane, select your database and right click.
Navigate to Task -> Import Flat File.
This will launch the import wizard and allow you to import your CSV file into your database.
Click Next to proceed to next step. In the next part, select the location of your CSV file, set your table name and select the schema.
You can leave the schema option as default.
Click on Next to preview the data. Make sure the data is as provided by selected CSV file.
The next step will allow you to modify various aspects of the table columns. For our example, let us set the id column as the primary key and allow null in the Country column.
With everything set, click Finish to start the import process. You will get success if the data has been imported successfully.
To confirm the data is inserted into the database, query the database as:
This should return the first 10 records from the csv file.
Bulk Insert Using T-SQL
In some instances, you do not get access to a GUI interface for importing and exporting data. Hence, it is important to learn how we can perform the above operation purely out of SQL queries.
The first step is to setup the database. For this one, we can call it bulk_insert_db_copy:
This should return:
Completion time: <>
The next step is to set up our database schema. We will refer to the CSV file to determine how to create our table.
Assuming we have a CSV file with the headers as:
We can model the table as shown:
Here, we create a table with the columns as the headers of the csv.
NOTE: Since the id value starts at a100 and increases by 1, we use the identity(100,1) property.
Learn more here: https://linuxhint.com/reset-identity-column-sql-server/
The last step is to insert the data. An example query is as shown below:
Here, we use the bulk insert query followed by the name of the table to which we wish to insert the data. Next is the from statement followed by the path to the CSV file.
Finally, we use the with clause to specify import properties. The first is firstrow which tells SQL server that the data starts at row 2. This is useful if your CSV file contains data header.
The second part is fieldterminator which specifies the delimiter for your CSV file. Keep in mind that there is no standard for CSV files, hence it can include other delimiters such as spaces, periods, etc.
The third part is rowterminator which describes one record in the CSV file. In our case one line = one record.
Running the code above should return:
You can verify the data exists by running the query:
This should return:
And with that, you have successfully, inserted a bulk CSV file to your SQL Server database.
This guide explores how to bulk insert data into a SQL Server database table or view. Check out our other great tutorial on SQL Server: