SQL Bulk Insert And Update Records Using Stored Procedures

Introduction

In this article, we will learn how to insert and update multiple records using a single stored procedure and user-defined table type. We will insert the records if the Id column value; i.e Primary key, doesn't exist and will update the records if the Id column value is duplicated. We will create a Stored procedure and User defined table type to implement this functionality also I will demonstrate how to call the stored procedure using SQL to test our implementation.

Note

In this article, I will show only how to call our created procedure using SQL Server. In my next article, I will demonstrate how to import the CSV or excel file to the table using the same Stored Procedure in SQL Server.

Prerequisite

Basic knowledge of SQL Server Database, Tables, and Stored Procedures. Find more about Tables in SQL Server here:  Tables in SQL Server

Find more about SQL Server here: SQL Server

Find more about Stored Procedures in SQL Server here: Stored Procedure in SQL Server

Step 1

First of all, we will create a Database named `BulkImportDemo`. Later on, we will use the same database to create the tables and procedures.

CREATE DATABASE BulkImportDemo  

Step 2

After creating the database we will create the table in the same database which we will use to demonstrate the bulk insert and update functionality.

USE BulkImportDemo  
  
CREATE TABLE Employee  
(  
    Id INT IDENTITY(1,1) PRIMARY KEY,  
    EmpName VARCHAR(35),  
    Position VARCHAR(50),  
    [Location] VARCHAR(50),  
    Age INT,  
    Salary DECIMAL  
)  

Here we have created a table named Employee, this table has the Id column as the primary key which is an auto-increment column. 

Step 3

Our employee table is ready to insert and update the data. Now we will create User defined table datatype. 

What is User Defined table type?

The user-defined table type is a custom user-defined datatype that will contain data in tabular format. We can use these datatypes to pass as parameters in stored procedure which is known as Table Valued Parameter.

We will create a user-defined table type for Employees as below.

CREATE TYPE tblTypeEmployee AS TABLE  
(  
    Id INT,  
    EmpName VARCHAR(35),  
    Position VARCHAR(50),  
    [Location] VARCHAR(50),  
    Age INT,  
    Salary DECIMAL  
)  

Here we created User defined table type. After executing the above command you can see this type in the Object Explorer of the BulkImportDemo database.

Programmability ==> Types ==> User-Defnied Table Types

Step 4

Now we will create the stored procedure to perform the insert and update operation. 

CREATE PROCEDURE spBulkImportEmployee  
(  
      @tblEmployeeTableType [dbo].tblTypeEmployee REadonly  
)  
AS  
BEGIN  
    MERGE Employee  AS dbEmployee  
    USING @tblEmployeeTableType AS tblTypeEmp  
    ON (dbEmployee.Id = tblTypeEmp.Id)  
  
    WHEN  MATCHED THEN  
        UPDATE SET  EmpName = tblTypeEmp.EmpName,   
                    Position = tblTypeEmp.Position,  
                    [Location]= tblTypeEmp.[Location],  
                    Age= tblTypeEmp.Age,  
                    Salary= tblTypeEmp.Salary  
  
    WHEN NOT MATCHED THEN  
        INSERT ([EmpName],[Position],[Location],Age,Salary)  
        VALUES (tblTypeEmp.EmpName,tblTypeEmp.Position,tblTypeEmp.[Location],tblTypeEmp.Age,tblTypeEmp.Salary);  
END  

Here we have created the procedure named `spBulkImportEmployee` which is accepting the table type created in an earlier step as a parameter, also known as a table-valued parameter.

We have used the `Merge` feature of the SQL server to perform updates and insert in the same query.  If the value of the Id column already exists in the table then it will update the rest of the field else it will insert the new record.

Step 5

Now we will see how to test the stored procedure (spBulkImportEmployee) created in an earlier step.

We have to create a variable of User-defined table type to pass it as a parameter of the procedure.

-- Declaring the variable of user-defined table type  
DECLARE @tblTypeEmployee tblTypeEmployee  
  
--Inserting some records  
INSERT INTO @tblTypeEmployee ([ID],[EmpName],[Position],[Location],Age,Salary)   
VALUES (0,'Cedric Kelly','Senior Javascript Developer','Edinburgh',22,43360)  
      ,(0,'Dai Riosy','Personnel Lead','London',22,43360)  
      ,(3,'Cara Stevens','Sales Assistant','Edinburgh',22,43360)  
      ,(0,'Thor Walton','Senior Developer','Sydney',27,217500)  
      ,(10,'Paul Byrd','Team Leader','Sydney',42,92575)  
      ,(6,'Finn Camacho','Software Engineer','California',34,372000)  
      ,(0,'Rhona Davidson','Integration Specialist','Newyork',37,725000)  
      ,(12,'Michelle House','Support Engineer','California',28,98540)  
  
  
-- Executing procedure  
EXEC spBulkImportEmployee  @tblTypeEmployee  

Here the first line is used to declare the variable of table type and the second line is used to insert some dummy records in the table type variable Lastly, we have called the `EXEC` to execute the procedure.

Conclusion

In this article, we learned about SQL bulk insert and update records Using Stored Procedures in SQL Server.

Thanks for reading this article. Let me know your feedback to enhance the quality of the article.