如何不使用临时表以不同方式插入数据？存储过程报错求助

嘿，我来帮你排查这个存储过程的问题，同时给你分享几种不依赖临时表插入数据的方式～

你的存储过程错误原因 #

你在INSERT语句里用了VALUES子句，但里面的子查询(SELECT t1.BUSINESSAREAID, t2.STAFFID, t3.ROLEID FROM t1,t2,t3)返回了3列数据，而VALUES里每个括号的位置只能对应目标表的一列，这就导致了列数不匹配的语法错误，数据库根本不知道怎么把这3列塞进一个列的位置里。

修正后的存储过程代码 #

我们只需要把INSERT ... VALUES改成INSERT ... SELECT，直接从CTE里取出需要的ID，再搭配传入的日期参数即可：

CREATE PROCEDURE setBARS 
-- Add the parameters for the stored procedure here 
@BUSINESSAREANAME nvarchar(50), 
@STAFFNAME nvarchar(50), 
@ROLENAME nvarchar(50), 
@BARSSTARTDATE date, 
@BARSENDDATE date 
AS 
BEGIN 
-- SET NOCOUNT ON added to prevent extra result sets from 
-- interfering with SELECT statements. 
SET NOCOUNT ON; 

WITH t1 (BUSINESSAREAID) AS (SELECT BUSINESSAREAID FROM BUSINESSAREA WHERE BUSINESSAREANAME = @BUSINESSAREANAME), 
t2 (STAFFID) AS (SELECT STAFFID FROM STAFF WHERE STAFFNAME = @STAFFNAME), 
t3 (ROLEID) AS (SELECT ROLEID FROM ROLE WHERE ROLENAME = @ROLENAME) 
INSERT INTO BARS ([BUSINESSAREAID],[STAFFID],[ROLEID],[BARSSTARTDATE],[BARSENDDATE]) 
SELECT t1.BUSINESSAREAID, t2.STAFFID, t3.ROLEID, @BARSSTARTDATE, @BARSENDDATE
FROM t1, t2, t3;
END 
GO

⚠️ 注意：这里t1,t2,t3是笛卡尔积关联，如果这三个表的查询结果都只有一行（比如名称唯一），那没问题；但如果存在同名的业务区域/员工/角色，会插入多条组合数据，你可以根据实际需求调整（比如用JOIN关联逻辑，或者加TOP 1限制）。

几种不使用临时表的插入数据方法 #

除了你用的CTE方式，还有这些常用方案：

1. 嵌套子查询直接插入 #

不需要定义CTE，把查询直接嵌套在INSERT的SELECT里，适合每个子查询仅返回一行的场景：

CREATE PROCEDURE setBARS 
@BUSINESSAREANAME nvarchar(50), 
@STAFFNAME nvarchar(50), 
@ROLENAME nvarchar(50), 
@BARSSTARTDATE date, 
@BARSENDDATE date 
AS 
BEGIN 
SET NOCOUNT ON; 

INSERT INTO BARS ([BUSINESSAREAID],[STAFFID],[ROLEID],[BARSSTARTDATE],[BARSENDDATE])
SELECT 
    (SELECT BUSINESSAREAID FROM BUSINESSAREA WHERE BUSINESSAREANAME = @BUSINESSAREANAME),
    (SELECT STAFFID FROM STAFF WHERE STAFFNAME = @STAFFNAME),
    (SELECT ROLEID FROM ROLE WHERE ROLENAME = @ROLENAME),
    @BARSSTARTDATE,
    @BARSENDDATE
-- 加这个条件是为了避免某个名称不存在时插入NULL值
WHERE EXISTS (SELECT 1 FROM BUSINESSAREA WHERE BUSINESSAREANAME = @BUSINESSAREANAME)
AND EXISTS (SELECT 1 FROM STAFF WHERE STAFFNAME = @STAFFNAME)
AND EXISTS (SELECT 1 FROM ROLE WHERE ROLENAME = @ROLENAME);
END 
GO

2. 表值构造函数（适合静态数据批量插入） #

如果是插入固定的静态数据，不需要从其他表取值，可以用这种方式一次性插入多条：

INSERT INTO BARS ([BUSINESSAREAID],[STAFFID],[ROLEID],[BARSSTARTDATE],[BARSENDDATE])
VALUES 
(1, 101, 201, '2024-01-01', '2024-12-31'),
(2, 102, 202, '2024-01-01', '2024-12-31'),
(3, 103, 203, '2024-01-01', '2024-12-31');

3. 直接关联查询插入 #

如果三个表之间有逻辑关联（比如员工属于某个业务区域），可以直接用JOIN关联后插入，避免笛卡尔积的问题：

CREATE PROCEDURE setBARS 
@BUSINESSAREANAME nvarchar(50), 
@STAFFNAME nvarchar(50), 
@ROLENAME nvarchar(50), 
@BARSSTARTDATE date, 
@BARSENDDATE date 
AS 
BEGIN 
SET NOCOUNT ON; 

INSERT INTO BARS ([BUSINESSAREAID],[STAFFID],[ROLEID],[BARSSTARTDATE],[BARSENDDATE])
SELECT 
    ba.BUSINESSAREAID,
    s.STAFFID,
    r.ROLEID,
    @BARSSTARTDATE,
    @BARSENDDATE
FROM BUSINESSAREA ba
JOIN STAFF s ON -- 这里可以加实际的关联条件，比如s.BUSINESSAREAID = ba.BUSINESSAREAID
JOIN ROLE r ON -- 同理，加合适的关联条件
WHERE ba.BUSINESSAREANAME = @BUSINESSAREANAME
AND s.STAFFNAME = @STAFFNAME
AND r.ROLENAME = @ROLENAME;
END 
GO

内容的提问来源于stack exchange，提问作者Sebastian