Code Web
Understanding the SQL Server Bulk Insert Command

Understanding the SQL Server Bulk Insert Command

Greg Bulmash's photo
Greg Bulmash
·

3 min read

In this article, I am going to explain the SQL server bulk insert and also you will learn how to custom your SQL Server procedure with this command.

Overview

The SQL Server bulk insert is a query language that helps us to store a million records into our database through a big CSV file and that can be done using a few options for this command.

Before getting started, we need to know what is the difference between the SQL and The SQL server.

The Difference Between the SQL and the SQL Server

As I mentioned before, SQL is a query language that can be used to handle and interact with the database through a few commands such as create, select, delete, and update.

Anyway, according to these four queries, we can do custom commands to retrieve or add or whatever to get the needed data.

On the other hand, the SQL Server is one of many database management systems that are helping the developers to use the SQL query language. And also for watching the live demo for the database tables according to their beautiful rows and columns inside, also you will see the organized data.

everything is very organized with the database management system.

Anyway, in the next section, we will take in-depth the SQL command and its options, Let's dive right in.

The SQL Server Bulk Insert Syntax

As I mentioned, we can send a big CSV file into the database table to store it at one time without any problem. Let's see the SQL bulk insert command.

   BULK INSERT [the_table_name] 
       FROM [the_file_path] 
       WITH [the_options]

Let's execute the previous command according to a real CSV file.

   BULK INSERT items 
       'D:\\products.csv' WITH (
       FORMAT = 'CSV' 
   );

But what is CSV? In the following few lines, you will understand the meaning and how to custom the options according to the SQL bulk insert command.

What is CSV?

The CSV is referring to a Comma Separated Values, a file containing plain text that represents a list of data according to their types. The file should end with the CSV extension such as – "sample.csv". The common uses options for the CSV file the below.

  BULK INSERT book FROM 'D:\\products.csv' WITH (
      FORMAT = 'CSV', -- format: the comma separated values
      ERRORFILE = 'D:\\error.txt'
  );

In the next section, you will use the SQL command in the SQL server procedure

Using SQL Server Bulk Insert with the Procedure

You will not able to use the same command in the SQL server procedure you have to do some changes like in the below code

  CREATE PROC proc_name 

           @file_path nvarchar(500)

        AS
        DECLARE @bulkquery NVARCHAR(2000)
        SET @bulkquery = N'BULK INSERT Financial_Survey FROM ''' + @file_path + N''' WITH (FORMAT= ''CSV'')';

   EXEC sp_executesql @bulkquery;

Wrapping UP

You understood the following lines SQL is a query language created to interact and handle the database CRUD operations. On the contrary, the SQL server is a database management system generated to help us use the SQL queries inside and to watch the organized data there.

Also, you learned how to use the SQL bulk insert command. And what does mean the CSV.

Since we created the CodedTag site, We are growing our materials to be a fit for your needs. Stay tuned for my next article.

Thanks for reading :)

SQL ServerSQL