Insert row with values from another row except for one?

Question

I am trying to create a stored procedure that will take a row with columns a,b,c,d where id = @id and insert a new row with the same values for a,b,c except d would be different.

In this case there are around 50 columns, though only the one column needs to be different.

Answer 1

Assuming d is an INT and you want to insert, say, 15 instead of whatever is in dbo.oldtable, then:

INSERT dbo.newtable(a,b,c,d) 
  SELECT a,b,c,d = 15
  FROM dbo.oldtable
  WHERE id = @id;

More likely it is from a variable, so:

INSERT dbo.newtable(a,b,c,d) 
  SELECT a,b,c,d = @whatever
  FROM dbo.oldtable
  WHERE id = @id;

Sorry, but there is no shorthand to say "all the columns except d"... you need to list them out separately. If typing is the problem, there is an easy way to deal with that. Open Object Explorer, expand your server, database, tables, and the table in question, then drag the columns node onto the query window:

Now you'll just have to delete the d column from that list.