Solved

Copying Records Using Stored Procedure

Posted on 2006-06-11
3
1,246 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 500 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now