Need to Copy a Master Record and its detail records

Question

Post reply

Need to Copy a Master Record and its detail records

mmaynard 31214

SSC Rookie

Points: 38
More actions
August 26, 2014 at 9:11 am

#311033

Hi all,
I have been asked to give the users the ability to copy a set of records within the database.
The current setup is
Master Table: JSA1
JSAID: (PK, int, not null)
JOBTITLE(nvarchar(200), null)
PlantNumber(int, not null)
Detail Table: tblSteps
STEPID (PK, int, not null)
JSAID (FK, int, not null)
StepNo (int, null)
BasicSteps (nvarchar(200), null)
DBPhoto(varbinary(max), null)
The plant number field is a location based field that the application uses to filter/select data on for the end users.
What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location. I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.
any help is greatly appreciated!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

For a single row, you can use SCOPE_IDENTITY().

For multiple rows (or a single row if you wish), you can use the OUTPUT clause.

Do you need more help?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mmaynard 31214 SSC Rookie Points: 38 More actions · Answer 2

Yes please.

This is why I have so far

USE [PlantSafety]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spCopyVWI]

(

@JSAID int,

@NewPlant int

)

AS

SET NOCOUNT OFF;

INSERT INTO [JSA1] ( [DATE_TIME], [JSA], [JOBTITLE], [PPE], [NewOrRev], [Company], [Plant], [nonRoutine], [Probability], [Exposure], [Consequence], [Time_Required], [PlantNumber] )

SELECT JSA1.DATE_TIME, JSA1.JSA, JSA1.JOBTITLE, JSA1.PPE, JSA1.NewOrRev, JSA1.Company, JSA1.Plant, JSA1.nonRoutine, JSA1.Probability, JSA1.Exposure, JSA1.Consequence, JSA1.Time_Required, @NEWPLANT AS NewPlant

FROM JSA1

WHERE (((JSA1.JSAID)=@JSAID))

GO

INSERT INTO tblsteps ( JSAID, STEPno, BasicSteps, HAZARDS, RECOMMEND, DBPhoto )

SELECT @@IDENTITY AS newID, tblsteps.STEPno, tblsteps.BasicSteps, tblsteps.HAZARDS, tblsteps.RECOMMEND, tblsteps.DBPhoto

FROM tblsteps

WHERE (((tblsteps.JSAID)=@JSAID))

GO

But it is giving me the error

Msg 137, Level 15, State 2, Line 5

Must declare the scalar variable "@JSAID".

isn't it already declared?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Yo need to remove the GO between both inserts. That's a batch separator and causes the variables to get out of scope.

Beware on the use of @@IDENTITY as it can return undesired results. That's why I suggested SCOPE_IDENTITY(). You can read about the differences if you click on the link.