SQL INSERT 搭配 SELECT 與常值混合插入：動態構建範例

場景

需將查詢結果與變數值合併寫入另一張表，例如試卷記錄表：題目來自 Test_Paper，其餘欄位為運行時變數。

Java 動態 SQL 範例

String selectSql = "SELECT id, " + uid + ", " + ddd + ", " + score +
                   ", '" + mark + "', " + markId + ", " + exam.getId() +
                   " FROM Test_Paper " +
                   "WHERE testBaseId = (SELECT id FROM Test_Base WHERE baseTestId = " + judgeMap.get(i).getId() +
                   " AND baseTestType = 1) AND paperId = " + paperBaseInfo.getId();

String insertSql = "INSERT INTO Test_Paper_Record (t_pid, userId, answer, score, mark, markerId, examId) " + selectSql;

等效 T-SQL 靜態寫法

INSERT INTO Test_Paper_Record (t_pid, userId, answer, score, mark, markerId, examId)
SELECT
    id,
    @uid,          -- 變數
    @ddd,
    @score,
    @mark,
    @markId,
    @examId
FROM Test_Paper
WHERE testBaseId = (SELECT id FROM Test_Base WHERE baseTestId = @judgeId AND baseTestType = 1)
  AND paperId = @paperId

關鍵點

• INSERT ... SELECT 後可直接跟常數/變數/運算式，順序必須與目標欄位列表一致。
• 動態拼接 SQL 時注意 SQL 注入，生產環境請改用參數化命令（PreparedStatement / sp_executesql）。
• 子查詢標量回傳 (SELECT id FROM ...) 需確保單列單值，否則會報錯。