如何创建存储过程实现批量插入多行数据到数据表？

要一次性向指定数据表插入多条数据，推荐使用表值参数的方案（灵活支持任意数量的行插入），以下是完整实现步骤：

1. 先定义匹配目标表结构的自定义表类型 #

-- 创建自定义表类型，用于传递批量数据
CREATE TYPE [dbo].[EmployeeType] AS TABLE (
    Name VARCHAR(20),
    Surname VARCHAR(20),
    Pay_Rate INT,
    Hire_Date DATE
);

2. 创建接受表值参数的存储过程 #

-- 创建批量插入存储过程
CREATE PROCEDURE [dbo].[Hire_Employee_Batch]
    @Employees [dbo].[EmployeeType] READONLY -- 表值参数必须设为READONLY
AS
BEGIN
    SET NOCOUNT ON; -- 屏蔽受影响行数的返回消息，提升执行效率
    -- 从表值参数中批量插入数据到目标表
    INSERT INTO [dbo].[Employees] (Name, Surname, Pay_Rate, Hire_Date)
    SELECT Name, Surname, Pay_Rate, Hire_Date FROM @Employees;
END;

3. 调用存储过程完成批量插入 #

-- 声明表值参数变量并填充多条数据
DECLARE @NewEmployees [dbo].[EmployeeType];
INSERT INTO @NewEmployees (Name, Surname, Pay_Rate, Hire_Date)
VALUES 
    ('Lance', 'Armstrong', 76, '2024-01-06'),
    ('Michael', 'Jackson', 60, '2024-01-07'),
    ('Kendrick', 'Lamar', 80, '2024-01-08'),
    ('Simone', 'Biles', 55, '2024-01-09');

-- 执行存储过程完成插入
EXEC [dbo].[Hire_Employee_Batch] @Employees = @NewEmployees;

关于原示例的问题说明 #

原示例的写法存在两个关键错误：

• 存储过程仅接受单组参数，实际插入的是4条完全相同的数据，无法实现插入不同记录的需求
• 执行存储过程时重复传递同名参数（如多个@Name）会触发SQL语法错误，因为同一参数不能被多次赋值

如果仅需固定插入指定数量的行（比如4条），也可以用多组独立参数的方式（扩展性较差，不推荐）：

-- 固定插入4条不同数据的存储过程
CREATE PROCEDURE [dbo].[Hire_Employee_Four]
    @Name1 VARCHAR(20), @Surname1 VARCHAR(20), @Pay_Rate1 INT, @Hire_Date1 DATE,
    @Name2 VARCHAR(20), @Surname2 VARCHAR(20), @Pay_Rate2 INT, @Hire_Date2 DATE,
    @Name3 VARCHAR(20), @Surname3 VARCHAR(20), @Pay_Rate3 INT, @Hire_Date3 DATE,
    @Name4 VARCHAR(20), @Surname4 VARCHAR(20), @Pay_Rate4 INT, @Hire_Date4 DATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [dbo].[Employees] (Name, Surname, Pay_Rate, Hire_Date)
    VALUES 
        (@Name1, @Surname1, @Pay_Rate1, @Hire_Date1),
        (@Name2, @Surname2, @Pay_Rate2, @Hire_Date2),
        (@Name3, @Surname3, @Pay_Rate3, @Hire_Date3),
        (@Name4, @Surname4, @Pay_Rate4, @Hire_Date4);
END;

-- 执行该存储过程
EXEC [dbo].[Hire_Employee_Four]
    @Name1 = 'Lance', @Surname1 = 'Armstrong', @Pay_Rate1 = 76, @Hire_Date1 = '2024-01-06',
    @Name2 = 'Michael', @Surname2 = 'Jackson', @Pay_Rate2 = 60, @Hire_Date2 = '2024-01-07',
    @Name3 = 'Kendrick', @Surname3 = 'Lamar', @Pay_Rate3 = 80, @Hire_Date3 = '2024-01-08',
    @Name4 = 'Simone', @Surname4 = 'Biles', @Pay_Rate4 = 55, @Hire_Date4 = '2024-01-09';

内容的提问来源于stack exchange，提问作者Michael.T