April 7, 2012 at 8:10 am
Does anyone have a basic example of the use of the OUTPUT CLAUSE in a MERGE Statement?
I found examples with INSERT & DELETE nut no UPDATE?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2012 at 11:28 am
Could someone please provide me with an example MERGE OUTPUT, INSER, UPDATE & DELETE?
$action
Is available only for the MERGE statement. Specifies a column of type nvarchar(10) in the OUTPUT clause in a MERGE statement that returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action that was performed on that row.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2012 at 11:46 am
I found an example of a Merge with the Output Clause.
I want to do something different, capture the @@RowCount of the Number of records affected, The table that the DML Operation was performed on and the Stored Procedure that performed the insert.
Any ideas would be greatly appreciated?
REATE TABLE FarmAnimals
(
AnimalID int IDENTITY PRIMARY KEY,
AnimalName nvarchar(50),
Price money)
GO
CREATE TABLE Pets
(
AnimalID int IDENTITY PRIMARY KEY,
AnimalName nvarchar(50),Price money
)
GO
INSERT FarmAnimals (AnimalName, Price)VALUES ('Goat', 250),('Sheep', 300)
GO
INSERT Pets (AnimalName, Price)VALUES ('Kitten', 75),('Puppy', 120), ('Goat', 350)
GO
MERGE INTO Pets USING FarmAnimals ON Pets.AnimalName = FarmAnimals.AnimalName
WHEN MATCHED THEN
UPDATE SET Price = FarmAnimals.Price
WHEN NOT MATCHED BY TARGET THEN
INSERT (AnimalName, Price) VALUES (FarmAnimals.AnimalName, FarmAnimals.Price)
OUTPUT $action,
inserted.AnimalName as ins_name,
deleted.AnimalName as del_name, deleted.Price as old_price;
--Results:
-- $action ins_name del_name old_price
-- ------- -------- -------- ---------
-- INSERT Sheep NULL NULL
-- UPDATE Goat
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2012 at 2:48 pm
As per the books online description of the OUTPUT clause the only output values are columns from the inserted or deleted tables plus scalar functions. Aggregate functions are not allowed in the OUTPUT.
Using the example given with pets and farmanimals, use the OUTPUT clause to output the changes made into a table variable then run an aggregate on that.
CREATE TABLE FarmAnimals(
AnimalID int IDENTITY PRIMARY KEY,
AnimalName nvarchar(50),
Price money
)
GO
CREATE TABLE Pets(
AnimalID int IDENTITY PRIMARY KEY,
AnimalName nvarchar(50),
Price money
)
GO
-- STRAIGHT OUTPUT
TRUNCATE TABLE FarmAnimals
TRUNCATE TABLE Pets
INSERT FarmAnimals (AnimalName, Price)
VALUES
('Goat', 250)
,('Sheep', 300)
INSERT Pets (AnimalName, Price)
VALUES
('Kitten', 75)
,('Puppy', 120)
,('Goat', 350)
DECLARE @OutputResults TABLE (
Action_Type varchar(50),
INS_Name nvarchar(50),
INS_Price money,
DEL_Name nvarchar(50),
DEL_Price money)
MERGE INTO Pets
USING FarmAnimals
ON Pets.AnimalName = FarmAnimals.AnimalName
WHEN MATCHED THEN
UPDATE SET Price = FarmAnimals.Price
WHEN NOT MATCHED BY TARGET THEN
INSERT (AnimalName, Price) VALUES (FarmAnimals.AnimalName, FarmAnimals.Price)
OUTPUT $action,inserted.AnimalName as ins_name,
inserted.Price as new_price,
deleted.AnimalName as del_name, deleted.Price as old_price
INTO @OutputResults;
SELECT * FROM @OutputResults
GO
-- SUMMARISED OUTPUT
TRUNCATE TABLE FarmAnimals
TRUNCATE TABLE Pets
INSERT FarmAnimals (AnimalName, Price)
VALUES
('Goat', 250)
,('Sheep', 300)
INSERT Pets (AnimalName, Price)
VALUES
('Kitten', 75)
,('Puppy', 120)
,('Goat', 350)
DECLARE @OutputResults TABLE (
Action_Type varchar(50),
INS_Name nvarchar(50),
INS_Price money,
DEL_Name nvarchar(50),
DEL_Price money)
MERGE INTO Pets
USING FarmAnimals
ON Pets.AnimalName = FarmAnimals.AnimalName
WHEN MATCHED THEN
UPDATE SET Price = FarmAnimals.Price
WHEN NOT MATCHED BY TARGET THEN
INSERT (AnimalName, Price) VALUES (FarmAnimals.AnimalName, FarmAnimals.Price)
OUTPUT $action,inserted.AnimalName as ins_name,
inserted.Price as new_price,
deleted.AnimalName as del_name, deleted.Price as old_price
INTO @OutputResults;
SELECT Action_type, count(*) as NumberRows, sum(INS_Price) as TotalAdded, sum(DEL_Price) as TotalDeleted
FROM @OutputResults
GROUP BY Action_Type
Fitz
April 7, 2012 at 3:00 pm
Thanks Mark.
What I need to capture is the total Number of records, Inserted, Updated & Deleted (count).
I do not care about the values inserted, updated or deleted.
I will also need aggregates on certain amount columns.
Thanks again for your help.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2012 at 3:11 pm
The final query is upto you. The @Outputresults holds all the records with the action type. Aggregate the changes by type and sum, min, max, avg the other columns as required.
If you need it in a single row then use a case statement or pivot.
Fitz
April 7, 2012 at 3:16 pm
Could you please add a delete to the example so that I have the syntax right?
I need to add the table name & Stored Procedure Name to the output.
The reason for the stored Procedure is that certain tables are loaded with multiple loads up to 21 SP's per table.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2012 at 4:41 pm
Try this for a demo of the MERGE you require. The DELETE something I would shy away from when using the MERGE as most of the time I would want the DestinationTable to hold all varsion of the row using validfrom and validto datetime fields as per slowly changing dimensions.
create table SourceTable(
ID int identity(1,1) primary key,
CustomerID varchar(30),
ProductID int,
Qty int,
Price money
)
go
create table DestinationTable(
ID int primary key,
CustomerID varchar(30),
ProductID int,
Qty int,
Price money
)
go
create table MergeChanges(
ChangeRecordID int identity(1,1) primary key,
TableName varchar(255),
ProcedureName varchar(255),
RunDT datetime,
ChangeType varchar(20),
NumberOfRecords int
)
go
create proc usp_PerformMerge @dt datetime
as
begin
declare @OutputResults as Table
(Action_Type varchar(50))
merge into dbo.DestinationTable
using dbo.SourceTable
on dbo.DestinationTable.ID = dbo.SourceTable.ID
-- only update rows where at least one value has changed
when matched and
(
dbo.SourceTable.CustomerID <> dbo.DestinationTable.CustomerID
OR dbo.SourceTable.ProductID <> dbo.DestinationTable.ProductID
OR dbo.SourceTable.Qty <> dbo.DestinationTable.Qty
OR dbo.SourceTable.Price <> dbo.DestinationTable.Price
)
then
update set CustomerID = dbo.SourceTable.CustomerID,
ProductID = dbo.SourceTable.ProductID,
Qty = dbo.SourceTable.Qty,
Price = dbo.SourceTable.Price
-- insert unmatched source rows into target
when not matched by Target then
insert (ID,CustomerID,ProductID,Qty,Price)
values (ID,CustomerID,ProductID,Qty,Price)
-- delete rows that are not matched in the source
when not matched by Source then
delete
-- record all action types in the @Outputresults
output $action INTO @OutputResults;
insert into dbo.MergeChanges
select 'DestinationTable',object_Name(@@PROCID),@DT,
Action_Type, count(*)
from @OutputResults
group by Action_Type
end
go
truncate table dbo.SourceTable
truncate table dbo.DestinationTable
truncate table MergeChanges
-- Insert initial data
insert into dbo.SourceTable values
('ABC',12,1000,2.30)
,('DEF',13,100,2.10)
,('ABC',14,50,4.50)
,('DEF',15,100,19.00)
,('FFF',16,40,20.00)
go
-- View the state of the tables so far
select * from dbo.SourceTable -- 5 records
select * from dbo.DestinationTable -- empty
select * from dbo.MergeChanges -- empty
go
-- Run the merge against an empty destination
exec usp_PerformMerge '2012-04-07 23:00'
go
-- View the state of the tables so far
select * from dbo.SourceTable -- 5 records
select * from dbo.DestinationTable -- 5 records
select * from dbo.MergeChanges -- 1 record (INSERT/5)
go
-- Make some changes (1 update, 1 delete, 1 insert)
delete from dbo.SourceTable where ID = 4
insert into dbo.SourceTable values ('XXX',12,3000,2.30)
update dbo.SourceTable set price = 19.50 where ID = 5
go
-- Run the update stored proc
exec usp_PerformMerge '2012-04-07 23:08'
go
-- Recheck the tables
select * from dbo.SourceTable -- 5 records
select * from dbo.DestinationTable -- 5 records
select * from dbo.MergeChanges -- 4 records
--RUN 1 = INSERT/5,
--RUN 2 = INSERT/1, DELETE/1, UPDATE/1
Fitz
April 7, 2012 at 10:41 pm
I'm wondering if I could add a Column to identify where the record was an INSERT, UPDATE OR DELETE? This would require editing the OPENQUERY Statement.
Any idea on the syntax to do this?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 8, 2012 at 2:21 am
It depends where the extra column is put.
If you want to have a source table with I, U and D indicators then you no longer require a merge statement, replace with 3 set based change queries.
If you want the last change to be indicated on the destination row then update the insert and update statements in the merge. The delete would not have an indicator as no row exists in the destination after the merge is run to hold the delete.
Fitz
April 8, 2012 at 3:09 pm
If I added the column it would be at the end of the table.
I need a way to tract how many Inserts, Updates and Deletes there are for each load as well as $ Amounts for certain fields.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 8, 2012 at 5:39 pm
The OUTPUT clause can get values from the tables used in the queries with the exception of the INSERT statement; INSERTED and DELETED can be used, but the base tables can also be used.
From http://technet.microsoft.com/en-us/library/ms177564.aspx:
<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
| $action
Notice that "from_table_name" is included in the options along with "INSERTED" and "DELETED".
Here is an example. Note that the table name alias "x." is included in the OUTPUT clause. I know I did not provide the code to duplicate the execution; I can create one if you need it:
;MERGETransACH u
USING(
SELECTb.StartupBonusID, a.AccountID, a.ACHRoutingNum, a.ACHAccountNum,
p.PersonID, CAST(REPLACE(p.FiscalID, '-', '') AS VARCHAR(20)) AS FiscalID,
s.TransAmt
FROMAccount a
JOINStartupPending s
ONs.AccountID = a.AccountID
JOINStartupBonus b
ONb.AccountID = s.AccountID
ANDb.BonusType = s.BonusType
JOINPerson p
ONp.AccountID = s.AccountID
ANDp.PrimaryInd <> 0
) x
ON1=2
WHENNOT MATCHED THEN
INSERT(--||||
CheckTypeCode,ComPerID,AccountID,PersonID,FiscalID,
TransAmt,ACHRoutingNum,ACHAccountNum,IssueDate,BalancedInd,
CreateDate,ChangeDate,ChangeUser,ProgName
)
VALUES(
'MON',180,x.AccountID,x.PersonID,x.FiscalID,
x.TransAmt,x.ACHRoutingNum, x.ACHAccountNum, '2012-03-28',0,
GETDATE(),GETDATE(),'JFahey','SSMS'
)
OUTPUTx.StartupBonusID, INSERTED.TransACHID
INTOStartupBonusDetail
;
Also, research the "$action" keyword that will tell you what action is performed. Perhaps you could output your records into a holding table, then perform a sum by the "$action" code included in the output.
April 8, 2012 at 5:46 pm
Could I get a @@rowcount and have a column for DML Indicator, 'Insert or 'Update' and stored that information in the Table?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 16, 2013 at 10:22 am
Will the following Statements capture Updates?
At the begining of the Merge I have:
DECLARE @TrackCounts TABLE ([ACTION] VARCHAR(20), INSERTEDID INT, DELETEDID INT);
At the end of the Mege I have this statement:
OUTPUT $ACTION, INSERTED.Record_Sequence, DELETED.Record_Sequence INTO @TrackCounts;
Then I insert into a Permanent Table.
Does this caputure Deletes? I do not have a Delete in my Merge Procedure.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 16, 2013 at 10:47 am
It works.:blush:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply