Solved

Copying Records Using Stored Procedure

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Time out only from one specific computer client 15 53
export sql server dbs 2 26
SQL - Load records to temp table through CTE 6 38
SQL Server Pivot 5 42
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

751 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