November 20, 2010 at 11:17 am
Hello,
I have a situation where i am using a merge statement in SQL SERVER 2008 and i want to due some extra calculation based on the inserted or deleted or updated values.
MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 10
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
Now suppose from the above example i want to delete records of the students whose values were inserted from a table named StudentAddress
How am i suppose to do that ?
I tried :
MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 10
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
DELETE FROM StudentAddress WHERE StudentAddress.StudentID=sd.StudentID
Its not working says 'The multi-part identifier "sd.StudentID" could not be bound.
Please Help
November 20, 2010 at 6:15 pm
mandar.dasgupta
Without table definitions, sample data it is difficult to assist you in a meaningful way. To receive a tested answer please post table definition(s), sample data and desired result following the examples and sample T-SQL contained in the article available by clicking on the first link in my signature block.
May I suggest that you consider the OUTPUT clause of the MERGE statement. Output $action, *.inserted ... etc.
<output_clause>::=
{
[ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
[ (column_list) ] ]
Then when the merge statement has completed, using the output table join that with your StudentAddress table to perform any additional tasks.
November 20, 2010 at 11:42 pm
Thank you for posting,
I will explain the whole process in detail.
I am working on a Insurance company software
I have a transaction table
transaction table
PolicyNo nvarchar(15) Primary Key,
Investor nvarchar(40),
Amount decimal(18,2),
PolicyDate Datetime,
AgentCode nvarchar(10),
LastModifiedOn Datetime
eg data :
PolicyNo Investor Amount PolicyDate AgentCode LastModifiedOn
1111 Shaun 1000 1/1/2010 750 1/2/2010
there is another table which stores the hierarchy of agents for that particular policy.
Hierarchy Table
PolicyNo nvarchar(15) ,
AgentCode nvarchar(10),
AgentRank int,
eg data :
PolicyNo AgentCode Rank
1111 750 11
1111 752 12
1111 753 13
1111 754 14
For policy no 111 the hieararchy chain of AgentCode 750 gets stored.
now what happens if the AgentCode in transaction table is changed
from 750 to 340 (ie. If it was a wrong input it needs to be corrected) the
details in the hierarchy table gets updated.
PolicyNo AgentCode Rank
1111 340 10
1111 341 12
1111 356 13
1111 367 14
Now we have a database at a local machine situated at a geographically different location from the server so if the database at the location gets updated we need to do it in the centralized server too. And since the server is not connected with the local machine.
I have to use the Merge statement using a table variable here is the code :
CREATE POCEDURE Append
(
@TableVariableTrans TypeTransaction READONLY,
@TableVariableHierarchy TypeHierarchy READONLY --(this is the hierarchy table from local machine)
)
MERGE Transaction AS T --(This is the server transaction table)
Using @TableVariableTrans AS TB --(This is the local transaction table)
ON T.PolicyNo = TB.PolicyNo
WHEN MATCHED AND TB.LastModifiedOn > T.LastModifiedOn THEN UPDATE
SET
T.PolicyNo =TB.PolicyNo
T.Investor=TB.Investor
T.Amount=TB.Amount
T.PolicyDate=TB.PolicyDate
T.AgentCode =TB.AgentCode
T.LastModifiedOn=TB.LastModifiedOn
-- I want to achieve the following situation
DELETE FROM Hierarchy Where PolicyNo=T.PolicyNo
--here i want to insert records in hierarchy table from the local hierarchy --table variable
Insert Into Hierarchy (PolicyNo,AgentCode, Rank)
Select PolicyNo,AgentCode, Rank From @TableVariableHierarchy
WHere PolicyNo=T.PolicyNo
Hope i made myself clear. Please Help
November 21, 2010 at 3:25 am
mandar.dasgupta (11/20/2010)
...Hope i made myself clear...
Nearly. It would help if you could summarise this in terms of source and target tables and their locations.
It's a little confusing because your first example mentions three tables, StudentTotalMarks as (UPDATE/INSERT) target, StudentDetails as source, and StudentAddress, which is neither and hence causes the MERGE to fail. MERGE uses just two table (sources), target and source. Bitbucket pointed out that you could use the OUTPUT clause of the MERGE statement to return the keys of the affected rows into a new table which could then be used as a filter for deletes from StudentAddress. OK so far (almost, there's a bug which affects the OUTPUT of a MERGE when the source target* is a table variable).
Your real-world scenario looks a little different. The MERGE is just an update of the Transaction table using @TableVariableTrans as source, unless you've missed part of it out. Either way, OUTPUT will provide you with the PolicyNo of the affected rows.
The statements following the merge are a DELETE followed by an INSERT FROM affecting the Hierarchy table, where keys match in @TableVariableHierarchy and keys also match affected rows from the update of the Transaction table.
Does this look about right?
* The MERGE bug reported by ALZDBA is thoroughly investigated and documented by Paul White in this blog entry. "1.The MERGE target must be a temporary table"
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 21, 2010 at 12:13 pm
well thanks a lot Chris,
but still i am confused. I will explain the situation even more detail later.
But for the time being can you tell me how will i use OUTPUT clause in a search criteria. I mean I want to delete only those PolicyNo from Hierarchy table which
were Inserted in the transaction table.
I tried something like this after the insert statement.
DELETE FROM Hierarchy WHere PolicyNo=OUTPUT Inserted.PolicyNo
But its not working.
November 21, 2010 at 12:52 pm
You want to the output $action, dleleted, insert and update items to be output to a temp file , then when the merge complete join the temp file to the table you want to delete from and go from their
November 21, 2010 at 12:57 pm
Well thanks bitbucket. Can explain lttle more can please you post some code or something showing how it is done.
November 21, 2010 at 1:41 pm
Read this, try to impliment the sample code, using a temporary file to receive the output. Once that is done do a Select * FROM the temp file to see what data is availabe, and then proceed from theire or come back for further assistance.
Use this link for guidance and how to info
November 21, 2010 at 4:54 pm
Here is a proper Merge statement from
http://technet.microsoft.com/en-us/library/bb510625.aspx
Here is the code itself, I believe you should be able to insert your table names, which seem to change, from post to post, so whatever they are:
USE AdventureWorks2008;
GO
CREATE TABLE #MyTempTable
(ExistingCode nchar(3),
ExistingName nvarchar(50),
ExistingDate datetime,
ActionTaken nvarchar(10),
NewCode nchar(3),
NewName nvarchar(50),
NewDate datetime);
CREATE PROCEDURE dbo.InsertUnitMeasure
@UnitMeasureCode nchar(3),
@Name nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
MERGE Production.UnitMeasure AS target
USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
ON (target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (UnitMeasureCode, Name)
VALUES (source.UnitMeasureCode, source.Name)
OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';
SELECT * FROM #MyTempTable
And here are the results:
ExistingCodeExistingNameExistingDateActionTakenNewCodeNewName NewDate
NULL NULL NULL INSERT ABCNew Test Value2010-11-21 18:35:55.437
NULL NULL NULL INSERT XYZTest Value2010-11-21 18:35:55.437
ABCNew Test Value2010-11-21 18:35:55.437UPDATEABCAnother Test Value2010-11-21 18:35:55.437
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply