How to copy the data from Main batch to content Batch?

Posted by Karthik2010_Mca under Sql Server on 7/12/2013 | Points: 10 | Views : 1672 | Status : [Member] | Replies : 1
Hi Everyone,
I need copy the data from Main batch to content Batch using sql stored procedure.

I have to mentioned the sp to incorporate following logic:
If child batch is empty ie no content exists in the child batch - you need to copy the main batch contents to the child batches.
If child batch has some content - you need to update those session content with you main batch content and insert rest of the main batch content in the child batch.

I want to alter the below sp for above mentioned logic. Kindly let me know if you know anything.

CREATE PROCEDURE [dbo].[uspCopyModuleBatchContent]
(
@I_Main_Batch_ID INT,
@I_Interim_Batch_ID INT,
@I_Term_ID INT,
@I_Module_ID INT,
@S_Crtd_By VARCHAR(20)=NULL,
@Dt_Crtd_On DATETIME=NULL
)
AS
BEGIN TRY

DECLARE @destinationBatchContent TABLE
( destinationBatchContentId INT
)

INSERT INTO @destinationBatchContent (destinationBatchContentId)
SELECT A.I_Batch_Content_Details_ID FROM dbo.T_Batch_Content_Details AS A ,dbo.T_Batch_Content_Details AS tbcd
WHERE A.I_Batch_ID= @I_Interim_Batch_ID AND tbcd.I_Batch_ID = @I_Main_Batch_ID AND A.I_Term_ID = tbcd.I_Term_ID AND A.I_Module_ID = tbcd.I_Module_ID

IF EXISTS(SELECT * FROM @destinationBatchContent)
BEGIN
UPDATE tbcd
SET tbcd.S_Session_Alias = tbcd1.S_Session_Alias,
tbcd.S_Session_Chapter = tbcd1.S_Session_Chapter,
tbcd.S_Session_Description = tbcd1.S_Session_Description,
tbcd.S_Content_URL = tbcd1.S_Content_URL,
tbcd.I_Folder_Id = tbcd1.I_Folder_Id,
tbcd.S_Upd_By =@S_Crtd_By ,
tbcd.Dt_Upd_On = @Dt_Crtd_On,
tbcd.B_IsActive = tbcd1.B_IsActive
FROM dbo.T_Batch_Content_Details AS tbcd INNER JOIN
dbo.T_Batch_Content_Details AS tbcd1
ON tbcd.I_Term_ID = tbcd1.I_Term_ID
AND tbcd.I_Module_ID = tbcd1.I_Module_ID
WHERE tbcd.I_Batch_ID = @I_Interim_Batch_ID
AND tbcd1.I_Batch_ID=@I_Main_Batch_ID

--DELETE dbo.T_Batch_Content_Details WHERE I_Batch_Content_Details_ID IN (SELECT destinationBatchContentId FROM @destinationBatchContent)

END
ELSE
BEGIN

INSERT INTO T_Batch_Content_Details (I_Batch_ID,I_Folder_Id ,I_Term_ID,I_Module_ID, I_Session_ID, S_Session_Alias, S_Session_Chapter, S_Session_Description, S_Content_URL, S_Crtd_By, Dt_Crtd_On,B_IsActive,S_Session_Name,S_Session_Topic)
SELECT @I_Interim_Batch_ID,I_Folder_Id,I_Term_ID, I_Module_ID, I_Session_ID, S_Session_Alias, S_Session_Chapter, S_Session_Description, S_Content_URL, @S_Crtd_By, @Dt_Crtd_On,B_IsActive,S_Session_Name,S_Session_Topic
FROM dbo.T_Batch_Content_Details AS tbcd WHERE I_Batch_ID=@I_Main_Batch_ID AND I_Term_ID=@I_Term_ID AND I_Module_ID=@I_Module_ID

--SELECT @destinationBatchContentId= @@IDENTITY
END

END TRY
BEGIN CATCH
DECLARE @ErrMsg NVARCHAR(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Karthik


Responses

Posted by: Bandi on: 7/29/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Karthik,
You have used T_Batch_Content_Details table only.. there is no Parent & Child table...
Can you provide sample data for above table.. and then explain what should be the output to your sample data...
I can provide you the solution/direction....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response