介绍

今天，我们将学习如何使用单个存储过程和用户定义的表类型插入和更新多条记录。我们将插入记录如果Id列值不存在，如果Id列值重复，将更新记录。我们将创建一个存储过程和用户定义的表类型来实现这个功能，我还将演示如何使用Sql调用存储过程来测试我们的实现。

先决条件

掌握SQL Server数据库、表和存储过程的基本知识。

步骤1

首先，我们将创建一个名为“BulkImportDemo”的数据库。稍后我们将使用相同的数据库创建表和过程。

CREATE DATABASE BulkImportDemo

步骤2

在创建数据库之后，我们将在同一个数据库中创建表，我们将使用该数据库来演示大容量插入和更新功能。

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  
)  

这里我们创建了一个名为Employee的表，这个表以Id列作为主键，它是自动递增的列。

步骤3

我们的employee表已经准备好插入和更新数据。现在我们将创建用户定义的表数据类型。

什么是用户定义的表类型?

用户定义的表类型是用户定义的数据类型，它将以表格格式包含数据。我们可以使用这些数据类型在存储过程中作为参数传递，称为表值参数。

我们将为Employee创建用户定义的表类型，如下所示。

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

这里我们创建了用户定义的表类型。执行上述命令后，您可以在BulkImportDemo数据库的对象资源管理器中看到此类型。

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

步骤4

现在我们将创建存储过程来执行插入和更新操作。

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 

这里我们创建了名为“spBulkImportEmployee”的过程，它接受前面步骤中创建的表类型作为参数，也称为表值参数。

我们使用SQL server的“Merge”特性在同一个查询中执行更新和插入。如果Id列的值已经存在于表中，那么它将更新字段的其余部分，否则将插入新记录。

步骤5

现在，我们将了解如何测试在前面步骤中创建的存储过程(spBulkImportEmployee)。

我们必须创建一个用户定义表类型的变量来传递它作为过程的参数。

-- 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 

这里第一行用于声明表类型变量，第二行用于在表类型变量中插入一些哑记录，最后调用'EXEC'来执行过程。