SQL Bulk Insert And Update Records Using Stored Procedures

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.