SQL Server中Excel数据导入多表：拆分tempInput至tbl1、tbl2遇tbl2插入问题

首先我先补全你提到的表结构（因为你给出的tbl1定义不完整），假设tbl1用于存储问题主体信息，tbl2存储每个问题的选项及正确标记：

1. 补全目标表结构 #

-- 完整的tbl1结构（自增主键question_id）
create table tbl1 (
 question_id int primary key identity(1,1),
 question_text nvarchar(100),
 description nvarchar(100)
)

-- tbl2用于存储选项，关联tbl1的question_id
create table tbl2 (
 option_id int primary key identity(1,1),
 question_id int foreign key references tbl1(question_id),
 option_text nvarchar(20),
 is_right bit -- 1表示正确选项，0表示错误
)

2. 第一步：将问题数据插入tbl1 #

为了准确关联后续的选项数据，我们先给tempInput加一个临时唯一标识，再通过OUTPUT子句记录插入后的question_id与原tempInput行的映射：

-- 给tempInput添加临时唯一ID，用于后续关联
ALTER TABLE tempInput ADD temp_id INT IDENTITY(1,1);

-- 创建临时表存储映射关系
CREATE TABLE #TempQuestionMapping (temp_id INT, question_id INT);

-- 插入tbl1并记录映射
INSERT INTO tbl1 (question_text, description)
OUTPUT ti.temp_id, inserted.question_id INTO #TempQuestionMapping
SELECT question_text, description
FROM tempInput ti;

3. 第二步：拆分选项数据插入tbl2 #

这里用UNION ALL把每个选项列拆分成独立行，同时通过临时映射表关联对应的question_id，并标记正确选项：

INSERT INTO tbl2 (question_id, option_text, is_right)
-- 处理option_1
SELECT 
    tqm.question_id,
    ti.option_1 AS option_text,
    CASE WHEN ti.right_option = 'option_1' THEN 1 ELSE 0 END AS is_right
FROM tempInput ti
JOIN #TempQuestionMapping tqm ON ti.temp_id = tqm.temp_id
WHERE ti.option_1 IS NOT NULL -- 过滤空选项
UNION ALL
-- 处理option_2
SELECT 
    tqm.question_id,
    ti.option_2 AS option_text,
    CASE WHEN ti.right_option = 'option_2' THEN 1 ELSE 0 END AS is_right
FROM tempInput ti
JOIN #TempQuestionMapping tqm ON ti.temp_id = tqm.temp_id
WHERE ti.option_2 IS NOT NULL
UNION ALL
-- 处理option_3
SELECT 
    tqm.question_id,
    ti.option_3 AS option_text,
    CASE WHEN ti.right_option = 'option_3' THEN 1 ELSE 0 END AS is_right
FROM tempInput ti
JOIN #TempQuestionMapping tqm ON ti.temp_id = tqm.temp_id
WHERE ti.option_3 IS NOT NULL
UNION ALL
-- 处理option_4
SELECT 
    tqm.question_id,
    ti.option_4 AS option_text,
    CASE WHEN ti.right_option = 'option_4' THEN 1 ELSE 0 END AS is_right
FROM tempInput ti
JOIN #TempQuestionMapping tqm ON ti.temp_id = tqm.temp_id
WHERE ti.option_4 IS NOT NULL;

注意事项 #

• 如果你的right_option存储的不是option_1这类字符串（比如是'1'、'A'等），需要调整CASE语句里的判断条件，比如CASE WHEN ti.right_option = '1' THEN 1 ELSE 0 END对应option_1。
• 如果question_text + description能保证唯一，也可以不用临时映射表，直接通过这两个字段关联tbl1和tempInput，但用临时ID的方式更可靠，避免重复数据导致关联错误。
• 执行完后可以清理临时对象：DROP TABLE #TempQuestionMapping; ALTER TABLE tempInput DROP COLUMN temp_id;

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