如何在SQL表中复制行并更改其中一个列值？

The answers for this question almost answer mine, but not quite. How do I turn this:

这个问题的答案几乎可以回答我的问题,但并不完全。我怎么转这个:

col0  col1 col2
data0 a    foo
data1 b    foo
data2 c    fee
data3 d    fee

into this? (duplicating the foo rows only)

进入这个? (仅复制foo行)

col0  col1 col2
data0 a    foo
data1 b    foo
data2 c    fee
data3 d    fee
data0 a    bar
data1 b    bar

Where bar is from the statement, not the data, and the original table has 2 new rows.

bar来自语句,而不是数据,原始表有2个新行。

4 个解决方案

#1

insert into T (col0, col1, col2)
select col0, col1, 'bar'
from T

If by "copy" you mean a select then a union would work as in other answers or you could try this:

如果通过“复制”表示选择,则联盟将像其他答案一样工作,或者您可以尝试:

select col0, col1, case when num = 0 then col2 else 'bar' end as col2
from T, (select 0 as num union all select 1) as dup

#2

One option, union all:

一个选项,联合所有:

select col0, col1, col2
from yourtable
union all
select col0, col1, 'bar'
from yourtable

#3

Assuming you just wanted the results for these two hard-coded strings, the following query would provide that for you.

假设您只想要这两个硬编码字符串的结果,以下查询将为您提供。

    SELECT
        col0,
        col1,
        'foo'
    FROM MyTable
UNION ALL
    SELECT
        col0,
        col1,
        'bar'
    FROM MyTable;

A more practical scenario is to use a temp table so you're not duplicating your query for each scenario.

更实际的情况是使用临时表,因此您不会为每个方案复制查询。

CREATE TABLE #Options
(
    col2 VARCHAR(50)
);

INSERT INTO #Options VALUES ('foo'), ('bar');

SELECT
    col0,
    col1,
    #Options.col2
FROM MyTable
CROSS JOIN #Options;

#4

select col0, col1, col2
from yourtable
union all
select col0, col1, 'bar'
from yourtable
where col2 = 'foo'

#1

insert into T (col0, col1, col2)
select col0, col1, 'bar'
from T