TSQL Stored Proc to copy records (with a twist!)

I am trying to write a Stored Procedure in SQL Server (2005) to do something that sounds simple, but is actually proving to be more difficult that I thought.

I have a table with 30 columns and 50,000 rows.

The number of records is fixed, but users can edit the fields of existing records.

To save them having to re-key repetitive data, I want to give them the ability to select a record, and specify a range of IDs to copy those details to.

The SP I'm trying to write will take 3 parameters: The source record primary key, and the lower and upper primary keys of the range of records that the data will be copied into.

Obviously the PKs of the destination records remain unchanged.

So I figur开发者_JAVA百科ed the SP needs to do a SELECT - to get all the data to be copied, and an UPDATE - to write the data into the specified destination records.

I just don't know how to store the results of the SELECT to slot them into the UPDATE. A temp table wouldn't help - selecting from that would be just the same as selecting from the table!

What I need is a variable that is effectively a single record, so I can go something like:

@tempRECORD = SELECT * FROM SOURCETABLE WHERE ID = @sourcePK

UPDATE SOURCETABLE
SET FIELD1 = @tempRECORD.FIELD1,  
  FIELD2 = @tempRECORD.FIELD2,  
  ...
  FIELD30 = @tempRECORD.FIELD30  
WHERE ID >= @LOWER_id AND ID <= @UPPER_id

But I don't know how, or if you even can.

I'm also open to any other clever way I haven't even thought of!

Thanks guys!

So I figured the SP needs to do a SELECT - to get all the data to be copied, and an UPDATE - to write the data into the specified destination records.

What you need is the T-SQL-specific extension to UPDATE, UPDATE ... FROM:

UPDATE T
SET
    Field1 = source.Field1
    , Field2 = source.Field2
    , Field3 = source.Field3
FROM
    (SELECT * FROM T AS source_T WHERE source_T.ID = @sourcePK) as source
WHERE
    T.ID BETWEEN @LOWER_Id AND @UPPER_Id

Note that this ability to put a FROM clause in an UPDATE statement is not standard ANSI SQL, so I don't know how this would be done in other RDBMSs.

I am pretty sure this ain't the easiest way to do it, but it should work without any problems:

DECLARE @tempField1 varchar(255)
DECLARE @tempField2 varchar(255)
...
DECLARE @tempField30 varchar(255)

SELECT @tempField1 = FIELD1, @tempField2 = FIELD2, ... ,@tempField30 = FIELD30 FROM SOURCETABLE WHERE ID = @sourcePK

UPDATE SOURCETABLE
SET FIELD1 = @tempField1,
  FIELD2 = @tempField2,  
  ...
  FIELD30 = @tempField30  
WHERE ID >= @LOWER_id AND ID <= @UPPER_id

You would need to edit the tempField variables so that they have the right type.