31

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.

iLemming
  • 34,477
  • 60
  • 195
  • 309

3 Answers3

64
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'
HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 2
    Ther is no circumstance in which you should use wildcards in an insert – HLGEM Mar 26 '12 at 21:18
  • How to do the same thing but with a small modification. Ex: i want to insert 'column2 val + test' instead of 'no' i mean to insert value from the swlwcted row with a text appended. – Shashank.gupta40 Apr 30 '15 at 07:25
  • 2
    @Shashank.gupta40 I know this is old, but for reference, provided that column2 is a 'string' type (varchar, nvarchar, etc), you can append a value by treating the column name like a variable and appending a new string, e.g. `column2 + 'test'`. – Michael L. Jan 25 '16 at 16:17
  • 1
    Note: In the example given, if the criterion " column2 = 'yes' " returns many rows, then many rows will get inserted. This can be quite handy, but the orig poster did ask about just one row, so this note is a caution. – Mark Goldfain Mar 30 '16 at 23:41
  • Without the `SELECT 'no'` and `WHERE column2 = 'yes'` we would get into an infinite loop. – Atom Sep 08 '16 at 15:51
  • if my table have 40 columns, do I need to write all of then? because the first example fit for me, but I need to change just 1 of 40 columns – patricK Aug 20 '20 at 20:07
7
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).

Jirka Hanika
  • 13,301
  • 3
  • 46
  • 75
  • 1
    Just to clarify, do your modifications as part of the select portion of the statement. – TimothyAWiseman Mar 26 '12 at 21:06
  • It depends on your needs. For example. Suppose your primary key is a meaningless integer with column name id without any key allocation mechanism (such as autoincrementing column); suppose that you want to copy the value of column1 from record with ID passed as @source, and set the value of column2 to a new value passed as @param. Then execute the following with the two parameters: `INSERT INTO t(id, column1, column2) SELECT m.id + 1, source.column1, @param FROM t m, t source WHERE source.id = @source AND m.id = (SELECT MAX(id) FROM t)` – Jirka Hanika Mar 27 '12 at 16:06
  • 1
    @Agzam - If, on the other hand, the id column is autoincrementing, it is all much simpler: `INSERT INTO t(column1, column2) SELECT source.column1, @param FROM t source WHERE source.id = @source` – Jirka Hanika Mar 27 '12 at 16:09
2

Say column is int 

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

You can multiply columns and perform string functions.

paparazzo
  • 44,497
  • 23
  • 105
  • 176