How can I concisely copy multiple SQL rows, with minor modifications?
- by Steve Jessop
I'm copying a subset of some data, so that the copy will be independently modifiable in future.
One of my SQL statements looks something like this (I've changed table and column names):
INSERT Product(
ProductRangeID,
Name, Weight, Price, Color, And, So, On
)
SELECT
@newrangeid AS ProductRangeID,
Name, Weight, Price, Color, And, So, On
FROM Product
WHERE ProductRangeID = @oldrangeid and Color = 'Blue'
That is, we're launching a new product range which initially just consists of all the blue items in some specified current range, under new SKUs. In future we may change the "blue-range" versions of the products independently of the old ones.
I'm pretty new at SQL: is there something clever I should do to avoid listing all those columns, or at least avoid listing them twice? I can live with the current code, but I'd rather not have to come back and modify it if new columns are added to Product. In its current form it would just silently fail to copy the new column if I forget to do that, which should show up in testing but isn't great.
I am copying every column except for the ProductRangeID (which I modify), the ProductID (incrementing primary key) and two DateCreated and timestamp columns (which take their auto-generated values for the new row).
Btw, I suspect I should probably have a separate join table between ProductID and ProductRangeID. I didn't define the tables.
This is in a T-SQL stored procedure on SQL Server 2008, if that makes any difference.
