Hi - I want to copy one or more existing records from a certain table, using those records to create new records in the same table, but change one of the field values in each record as it is created.
Here's the scenario - I have a products table and a features table. Each product record has a number of feature records associated with it. I want to search the features table for a group of records matching one ProductID, copy them, insert them as new records in the features table, and append the ProductID field to reflect a different product ID.
I can copy records OK with this:
CREATE PROCEDURE spCopyFeatures
INSERT INTO dbo.tblFeatures (MfgID, SrsID, ProID, rlFeaCatSeq, rlFeaSeq, nvcFeaName, ntFeaDesc)
SELECT MfgID, SrsID, ProID, rlFeaCatSeq, rlFeaSeq, nvcFeaName, ntFeaDesc FROM dbo.tblFeatures WHERE ProductID = @ProdID
I'm new to SQL and stored procedures beyond the easy stuff. How can I simultaneously edit the "ProID" field with a product ID I supply (different from the product ID used above to search the features table)?