How can update and insert in the same table [Resolved]

Hi Karthik,
Try to understand the below queries and tweet your procedure accordingly....
-- Sample data of both Main & Child Tables

DECLARE @updateandInsertMain table ( id int, ComId int, Name CHAR(1))

insert into @updateandInsertMain 

SELECT 1, 1, 'A' union all

SELECT 2, 1, 'B' union all 

SELECT 3, 1, 'C' union all 

SELECT 4, 1, 'D' union all 

SELECT 5, 2, 'E' union all 

SELECT 6, 2, 'F' 



DECLARE @updateandInsertChild table (id int,  ComId int ,Name CHAR(1))

insert into @updateandInsertChild

SELECT 1, 1, 'E' union all

SELECT 2, 1, 'F' union all 

SELECT 3, 1, 'G' union all 

SELECT 4, 1, 'H'  



--Case1 

INSERT INTO @updateandInsertChild 

SELECT m.id, m.comid, m.Name

FROM @updateandInsertMain m

LEFT JOIN @updateandInsertChild c ON m.id = c.id

WHERE c.ID IS NULL

/*By using above INSERT statement you can insert 5th & 6th rows to child table
id ComId Name
5 2 E
6 2 F*/
SELECT * FROM @updateandInsertMain
SELECT * FROM @updateandInsertChild
--------------------------------------------------------------------
-- Case2:
-- The below MERGE statement is for UPDATE as well as INSERT to child table...

MERGE @updateandInsertChild c  -- This is the target(Child) table

USING @updateandInsertMain m  -- This is the Parent(Source) Table

ON c.id = m.id  -- Conditions whatever you want here

WHEN MATCHED THEN UPDATE SET c.Name = m.Name, c.comid=m.comid  -- Whenever there is corresponding ID in both tables then UPDATE will take place

WHEN NOT MATCHED THEN INSERT VALUES( m.id, m.comid, m.name);  -- If there is no matching ID in Child table then attempts to INSERT New row to child table from parent



SELECT * FROM @updateandInsertChild 

/*OUTPUT of child Table:
id ComId Name
1 1 A -- UPDATE happend ( Name E to A)
2 1 B -- UPDATE happend ( Name F to B)
3 1 C -- UPDATE happend ( Name G to C)
4 1 D -- UPDATE happend ( Name H to D)
5 2 E -- INSERT happend
6 2 F -- INSERT happend
*/

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

UPDATE b
SET b.name = a.namw
FROM updateandinser1 a
JOIN updateandinsert2 b
ON a.id=b.id AND a.comid = b.comid

explanation:
assume that updateandinsert1 and updateandinsert2 as two tables(Main table & Child table)
try the above UPDATE statement...
i havn't understood your requirement.
if NOT the above can you post your exact output with sample data

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

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

To do update & insert at a time based on consition we can make use of MERGE statement..
MERGE childupdateandinsert AS up
USING parentupdateandinsert AS pup
ON up.id=pup.id AND id.comid = pup.comid
WHEN MATCHED THEN
UPDATE SET up.Name=pup.name

WHEN NOT MATCHED THEN INSERT VALUES(pup.id,pup.comid, pup.name)
note: the above query is for both insert & update..
http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

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