Copying records in same table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copying records in same table

Author

Topic

SCADA_Monkey
Starting Member

3 Posts

Posted - 2007-03-07 : 15:32:25

Before I start asking for favours I think it only polite that I introduce myself. My name is Doug I'm a EC&I engineer from the UK.

I'm developing a recipe managment system with SQL Server 2005 at the database system. I have a table which contains recipe parameters for an industrial process. I would like to generate a stored procedure which will copy either all or just some of the fields from one record in a recipe table to another record in the same table while retaining the unique recipe id. For example,
CREATE PROCEDURE sp_CopyRecipes
@RecipeNumberCopyFrom int
@RecipeNumberCopyTo int
AS
SELECT Param1,Param2,Param3 FROM tblRecipe
WHERE
RecipeID = @RecipeNumberCopyFrom
(And Copy to)
SELECT Param1,Param2,Param3 FROM tblRecipe
WHERE
RecipeID = @RecipeNumberCopyTo

there are 45 recipe paramters. There is a requirement to copy all paramters or just a selection. I written a procedure in the VB.NET front end which works well, but I'd prefer it to be done on the DB server. Thanks in advance
Hope I can contribute in the future

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 16:48:22

[code]
update t
set Param1 = f.Param1,
Param2 = f.Param2,
Param3 = f.Param3
from tblRecipe t inner join tblRecipe f
on t.RecipeID = @RecipeNumberCopyTo
and f.RecipeID = @RecipeNumberCopyFrom
[/code]

SCADA_Monkey
Starting Member

3 Posts

Posted - 2007-03-08 : 14:42:35

Worked a treat, thank you so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources