SQL Bulk Insert And Update Records Using Stored Procedures

Introduction

 
Today we will learn how to insert and update multiple records using single stored procedure and user defined table type. We will insert the records if Id column value; i.e Primary key, doesn't exist and will update the records if 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 table using the same stored procedure.
 
Prerequisite
 
Basic knowledge of SQL Server Database , Tables and Stored Procedures.
 
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.
  1. 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.
  1. USE BulkImportDemo  
  2.   
  3. CREATE TABLE Employee  
  4. (  
  5.     Id INT IDENTITY(1,1) PRIMARY KEY,  
  6.     EmpName VARCHAR(35),  
  7.     Position VARCHAR(50),  
  8.     [Location] VARCHAR(50),  
  9.     Age INT,  
  10.     Salary DECIMAL  
  11. )  
Here we have created table named Employee , this table has Id column as primary key which is 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 ?
 
User defined table type is cutom user defined datatype which will contain data in tabular format. We can use these datatypes to pass as parameter in stored procedure which is known as Table Valued Parameter.
 
We will create user defined table type for Employee as below.
  1. CREATE TYPE tblTypeEmployee AS TABLE  
  2. (  
  3.     Id INT,  
  4.     EmpName VARCHAR(35),  
  5.     Position VARCHAR(50),  
  6.     [Location] VARCHAR(50),  
  7.     Age INT,  
  8.     Salary DECIMAL  
  9. )  
Here we created User defined table type. After executing above command you can see this type in Object Explorer of BulkImportDemo database.
 
Programmability ==> Types ==> User-Defnied Table Types
 
Step 4
 
Now we will create the stored procedure to perform the insert and update operation. 
  1. CREATE PROCEDURE spBulkImportEmployee  
  2. (  
  3.       @tblEmployeeTableType [dbo].tblTypeEmployee REadonly  
  4. )  
  5. AS  
  6. BEGIN  
  7.     MERGE Employee  AS dbEmployee  
  8.     USING @tblEmployeeTableType AS tblTypeEmp  
  9.     ON (dbEmployee.Id = tblTypeEmp.Id)  
  10.   
  11.     WHEN  MATCHED THEN  
  12.         UPDATE SET  EmpName = tblTypeEmp.EmpName,   
  13.                     Position = tblTypeEmp.Position,  
  14.                     [Location]= tblTypeEmp.[Location],  
  15.                     Age= tblTypeEmp.Age,  
  16.                     Salary= tblTypeEmp.Salary  
  17.   
  18.     WHEN NOT MATCHED THEN  
  19.         INSERT ([EmpName],[Position],[Location],Age,Salary)  
  20.         VALUES (tblTypeEmp.EmpName,tblTypeEmp.Position,tblTypeEmp.[Location],tblTypeEmp.Age,tblTypeEmp.Salary);  
  21. 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 table valued parameter.
 
We have used `Merge` feature of SQL server to perform update and insert in same query.  If value of Id column already exists in 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 parameter of procedure.
  1. -- Declaring the variable of user defined table type  
  2. DECLARE @tblTypeEmployee tblTypeEmployee  
  3.   
  4. --Inserting some records  
  5. INSERT INTO @tblTypeEmployee ([ID],[EmpName],[Position],[Location],Age,Salary)   
  6. VALUES (0,'Cedric Kelly','Senior Javascript Developer','Edinburgh',22,43360)  
  7.       ,(0,'Dai Riosy','Personnel Lead','London',22,43360)  
  8.       ,(3,'Cara Stevens','Sales Assistant','Edinburgh',22,43360)  
  9.       ,(0,'Thor Walton','Senior Developer','Sydney',27,217500)  
  10.       ,(10,'Paul Byrd','Team Leader','Sydney',42,92575)  
  11.       ,(6,'Finn Camacho','Software Engineer','California',34,372000)  
  12.       ,(0,'Rhona Davidson','Integration Specialist','Newyork',37,725000)  
  13.       ,(12,'Michelle House','Support Engineer','California',28,98540)  
  14.   
  15.   
  16. -- Executing procedure  
  17. 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 table type variable and at last we have called the `EXEC` to execute the procedure.
 
Thanks for reading this article. Let me know your feedback to enhance the quality of article. In my next article I will demonstrate how to call this stored procedure through Asp.Net MVC Application.