Hi,
Already I have updated in above mentioned scenario. But I want insert data not in the child.
For Example I have 6 row. 1st 4 row, I have updated and data is not present in the remaining 5th and 6th row. So I need insert 5th and 6th row.
updateandInsert(Main table)
id ComId Name
1 1 A
2 1 B
3 1 C
4 1 D
5 2 E
6 2 F
updateandInsert(child table)
id ComId Name
1 1 E
2 1 F
3 1 G
4 1 H
5 2 -
6 2 -
o/p like below
updateandInsert(Main table)
id ComId Name
1 1 A --Update from Main table to child
2 1 B --Update from Main table to child
3 1 C --Update from Main table to child
4 1 D --Update from Main table to child
5 2 E --Insert from Main table to child
6 2 F --Insert from Main table to child
Ex:
ALTER 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 AND
tbcd.I_Term_ID = @I_Term_ID AND
tbcd.I_Module_ID=@I_Module_ID
--Here I want insert data for the above requirement
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
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
Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator