Select, Modify and insert into the same table

问题:

What's the easiest way to select a row, modify couple of columns and insert it to the same table? I'm trying to insert a new row based on another.

回答1:

INSERT INTO table2 (column1, column2, column3)
SELECT column1, 'no', column3 FROM table2 WHERE column2 = 'yes'

Hopefully this is a bit clearer as to how you do this. As you can see, I've grabbed two columns from table2 and for the other column I used a text value for instead of the value for column2.

Other patterns you can use:

Combine a column and some other text (Assumes the column is already a string data type.

INSERT INTO table2 (column1, column2)
SELECT column1 + 'no', column2 FROM table2 WHERE column2 = 'yes'

Combine a column and some text, One example where the column is a string and one where it is not.

INSERT INTO table2 (column1, column2)
SELECT column1 + 'no', 'A' + cast(column2 as Varchar (10)) FROM table2 WHERE column2 = 'yes'

回答2:

INSERT INTO table(column1,column2) SELECT column1, const2 FROM table ...

The select list will likely mix copied columns (by name) and changed columns (by desired values).

回答3:

Say column is int

INSERT INTO table(column1,column2) SELECT column1 + 1, column2 -1 FROM table

You can multiply columns and perform string functions.