Solved

Copying Records Using Stored Procedure

Posted on 2006-06-11
3
1,253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

617 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