August 26, 2013 at 7:06 am
Hi all,
I have a scenario:
There is 2 tables tableA and tableB. If I insert any record in tableA that is not exist in tableB then that record should get insert into tableB also, else if a record exists and any value is updated for that record than that value should also get updated in tableB.
I have written MERGE statement for this in an trigger... But I am confused which should I use for this
please suggest
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 26, 2013 at 7:07 am
Will this script work to accomplish my scenario?
Create Trigger Trg_DeviationRequestDetails_Ins
ON DeviationRequestDetails
After INSERT AS
BEGIN
MERGE INTO CLCProcessUnitDetailsCompany T
USING (SELECT drd.ProjectID, drd.ProposalID, drd.SolutionID,drd.UnitID
FROM DeviationRequestDetails drd)S
ON S.ProjectID = T.ProjectID AND
S.ProposalID = T.ProposalID AND
S.SolutionId = T.SolutionId AND
S.UnitID = T.UnitID
WHEN MATCHED THEN
UPDATE SET T.DevDateChanged = Getdate()
WHEN NOT MATCHED THEN
INSERT (
ProjectID,
ProposalID,
SolutionID,
UnitID,
DevDateChanged,
QuotDateChanged,
ApprovalDateChanged,
AddedBy,
DateAdded,
ChangedBy,
DateChanged
)
VALUES
(
S.ProjectID,
S.ProposalID,
S.SolutionID,
S.UnitID,
Getdate(),
NULL,
NULL,
S.AddedBy,
S.DateAdded,
S.ChangedBy,
S.DateChanged
);
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 26, 2013 at 7:09 am
Seems like an AFTER trigger to me.
Also seems table B is just a copy of table A 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2013 at 7:11 am
Regarding the script you posted (while I was typing my previous reply), you don't use the INSERTED or DELETED system tables. Those might come in handy.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2013 at 7:26 am
Koen Verbeeck (8/26/2013)
Regarding the script you posted (while I was typing my previous reply), you don't use the INSERTED or DELETED system tables. Those might come in handy.
is it necessary to use magic tables?
will my script dont work?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 26, 2013 at 7:29 am
kapil_kk (8/26/2013)
Koen Verbeeck (8/26/2013)
Regarding the script you posted (while I was typing my previous reply), you don't use the INSERTED or DELETED system tables. Those might come in handy.is it necessary to use magic tables?
Unless you want to affect the entire table, rather than the rows that have changed, yes
will my script dont work?
It'll work. Very inefficiently, very slowly, but it'll work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2013 at 7:31 am
For table CLCProcessUnitDetailsCompany which is blank right now..
Create table CLCProcessUnitDetailsCompany
(
ProjectID int NOT NULL,
ProsposalID int NOT NULL,
SolutionID int not null,
UnitID int NOT NULL,
DevDateChanged DATETIME,
QuotDateChanged DATETIME,
ApprovalDateChanged DATETIME,
AddedBy nvarchar(50),
DateAdded datetime,
ChangedBy nvarchar(50),
DateChanged datetime,
CONSTRAINT PK_CLCProcessUnitDetailsCompany PRIMARY KEY CLUSTERED
(
ProjectID,
ProsposalId,
SolutionID,
UnitID
)
)
when I insert any data in DeviationRequestDetails
it gives me this error:
Violation of PRIMARY KEY constraint 'PK_CLCProcessUnitDetailsCompany'. Cannot insert duplicate key in object 'dbo.CLCProcessUnitDetailsCompany'. The duplicate key value is (18, 1, 1, 1).
Whats wrong with the structure
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 26, 2013 at 7:40 am
You are trying to insert duplicates.
Check the source data and your code to make sure you are not duplicating rows somewhere.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2013 at 7:43 am
Koen Verbeeck (8/26/2013)
You are trying to insert duplicates.Check the source data and your code to make sure you are not duplicating rows somewhere.
even when I am doing any updation in source table it is giving me same error
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 26, 2013 at 7:44 am
Koen Verbeeck (8/26/2013)
Regarding the script you posted (while I was typing my previous reply), you don't use the INSERTED or DELETED system tables. Those might come in handy.
Can you please tell me how can i convert this script with use of magic tables:
Alter Trigger Trg_DeviationRequestDetails_Ins
ON DeviationRequestDetails
After INSERT, UPDATE AS
BEGIN
MERGE INTO CLCProcessUnitDetailsCompany T
USING DeviationRequestDetails S
ON S.ProjectID = T.ProjectID AND
S.ProposalID = T.ProposalID AND
S.SolutionId = T.SolutionId AND
S.UnitID = T.UnitID
WHEN MATCHED THEN
UPDATE SET T.DevDateChanged = Getdate()
WHEN NOT MATCHED THEN
INSERT (
ProjectID,
ProposalID,
SolutionID,
UnitID,
DevDateChanged,
QuotDateChanged,
ApprovalDateChanged,
AddedBy,
DateAdded,
ChangedBy,
DateChanged
)
VALUES
(
S.ProjectID,
S.ProposalID,
S.SolutionID,
S.UnitID,
Getdate(),
NULL,
NULL,
S.AddedBy,
S.DateAdded,
S.ChangedBy,
S.DateChanged
);
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 26, 2013 at 7:53 am
I have changed my code into this and used magic table INSERTED and its working now:)
Is it ok?
Alter Trigger Trg_DeviationRequestDetails_Ins
ON DeviationRequestDetails
After INSERT, UPDATE AS
BEGIN
MERGE INTO CLCProcessUnitDetailsCompany T
USING INSERTED as S
ON S.ProjectID = T.ProjectID AND
S.ProposalID = T.ProposalID AND
S.SolutionId = T.SolutionId AND
S.UnitID = T.UnitID
WHEN MATCHED THEN
UPDATE SET T.DevDateChanged = Getdate()
WHEN NOT MATCHED THEN
INSERT (
ProjectID,
ProposalID,
SolutionID,
UnitID,
DevDateChanged,
QuotDateChanged,
ApprovalDateChanged,
AddedBy,
DateAdded,
ChangedBy,
DateChanged
)
VALUES
(
S.ProjectID,
S.ProposalID,
S.SolutionID,
S.UnitID,
Getdate(),
NULL,
NULL,
S.AddedBy,
S.DateAdded,
S.ChangedBy,
S.DateChanged
);
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 26, 2013 at 1:14 pm
Seems fine to me. All nice set-based operations 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2013 at 10:43 pm
Koen Verbeeck (8/26/2013)
Seems fine to me. All nice set-based operations 🙂
🙂 :-P:-D:-P
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply