Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Copying Records Using Stored Procedure

Posted on 2006-06-11
3
Medium Priority
?
1,257 Views
Last Modified: 2012-06-27
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
     @ProductID Integer
AS
BEGIN
     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
END

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)?

Thanks,

Rod
0
Comment
Question by:okanagan
3 Comments
 
LVL 33

Accepted Solution

by:
sajuks earned 2000 total points
ID: 16882990
change your select to
   SELECT MfgID, SrsID, ProID +  @ProductID, rlFeaCatSeq, rlFeaSeq, nvcFeaName, ntFeaDesc FROM dbo.tblFeatures WHERE ProductID = @ProdID
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16882995
okanagan,
> How can I simultaneously edit the "ProID" field with a product ID I supply

You need to pass the new productId's as argument
0
 

Author Comment

by:okanagan
ID: 16883063
Thanks sajuks - right on the money.

Rod
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question