How do you copy a row in a SQL table and alter one of the column values?

Question

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)

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.

Answer 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

Answer 2

One option, union all:

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

Answer 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;

Answer 4

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