How to insert a row with two columns modified

Question

I didn't design this table; I just have to work with it. And modifying the design isn't really possible.

The table has ~50 columns. I want to copy a row, update two of the columns (a GUID and an int), and then insert it back into the table.

I can't seem to come up with how to do this without listing all the columns. Does someone have any techniques?

See the answer by Jonas as the best solution to your question — Peter M, Mar 17 '15 at 22:01
Using a temp table means you only have to specify the columns being changed. But it does mean multiple queries — Peter M, Mar 17 '15 at 22:02
Search this site by using statement: "Update + Select" ;) To update 2 columns you do not need to get entire row! — Maciej Los, Mar 17 '15 at 22:04
If typing is your primary concernt, in SQL Server Management studio you can drag the columns onto the query designer, meaning you don't need to type them all out. There is a visual demonstration in the article [Bad habits to kick : using SELECT * / omitting the column list](https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list) by Aaron Bertrand. There are hacks for this such as selecting into a temp table (as described in the linked answer), but you will run into issues with computed columns and identity columns. — GarethD, Mar 17 '15 at 22:04
You might also try selecting from `syscolumns`, you can join on `sysobjects` and specify the table name, then syscolumns will give you the column names. In this manner you could generate your sql statement in sql. — crthompson, Mar 17 '15 at 22:05

score -1 · Answer 1 · answered Mar 17 '15 at 22:04

-1

insert into table_name (d1, d2,......)
select d1, d2, ...
from table_name
where unique_id = 'value'

answered Mar 17 '15 at 22:04

m2j

1

OP defined his issue as: `I want to copy a row, update two of the columns and then insert it back into the table.` So, INSERT + SELECT does not meet his needs ;( – Maciej Los Mar 17 '15 at 22:06
he can UPDATE the table after copying – m2j Mar 17 '15 at 22:07
1

I don't get you, especially this: "after copying"... – Maciej Los Mar 17 '15 at 22:10
UPDATE tickets SET tickets.ticket_number=( SELECT max_ticket FROM ( SELECT (MAX(ticket_number)+1) AS max_ticket FROM tickets ) AS sub_table) WHERE ticket_id=12345; @fuv answer – m2j Mar 17 '15 at 22:13

1 Answers1