sql - 插入删除的数据，加上一些硬编码值，输出语句

Question

我想将行从一个表移动到另一个表，delete from [foo] output deleted.[col] into [bar] (col) 看起来是个不错的选择。

但是这些列并不相同。因此，我想将一些硬编码值(理想情况下是通过编程确定的值)插入到目标表中。

我设置了几个表来演示。

create table delete_output_test (
    thing1 int not null,
    thing2 varchar(50),
    thing3 varchar(50)
)

create table delete_output_test2 (
    thing1 int not null,
    thing2 varchar(50),
    thing3 varchar(50),
    thing4 int
)

insert into delete_output_test values (0, 'hello', 'world'),
                                      (1, 'it''s', 'me'),
                                      (2, 'i', 'was'),
                                      (3, 'wondering', 'if')

现在，如果我不太需要的话，从一张 table 搬到另一张 table 就可以了......

delete from delete_output_test2
output deleted.thing1,
       deleted.thing2,
       deleted.thing3
into delete_output_test
       (thing1,
        thing2,
        thing3)

但是如果我想填充最后一列怎么办？

delete from delete_output_test2
output deleted.thing1,
       deleted.thing2,
       deleted.thing3
into delete_output_test
       (thing1,
        thing2,
        thing3,
        4)

Incorrect syntax near '4'. Expecting '.', ID, PSEUDOCOL, or QUOTED_ID.

我是 SQL 的新手，所以我什至不确定那些东西是什么。

那么为什么我不能硬编码一个值来插入呢？如果我想变聪明的话，甚至可以用一些 select 语句替换 4？

Answer 1

嗯，delete_output_test 没有名为 4 或 thing4 的列，但 delete_output_test2 有。所以你可以这样做:

delete from delete_output_test
output deleted.thing1,
       deleted.thing2,
       deleted.thing3,
       4
into delete_output_test2
       (thing1,
        thing2,
        thing3,
        thing4);
select * from delete_output_test2;

rextester 演示:http://rextester.com/CVZOB61339

返回:

+--------+-----------+--------+--------+
| thing1 |  thing2   | thing3 | thing4 |
+--------+-----------+--------+--------+
|      0 | hello     | world  |      4 |
|      1 | it's      | me     |      4 |
|      2 | i         | was    |      4 |
|      3 | wondering | if     |      4 |
+--------+-----------+--------+--------+