Merge 2 queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Merge 2 queries

Author

Topic

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2012-02-22 : 12:45:46

Hi,

I have table,FloatBalance,Floatbalance_Log, Allloads.
I am performing two operation mentioned below every minute.

The record are inserted in allloads table continuously like 10 records
per second.

1) -- Update the float balances from the all loads table.

UPDATE FloatBal
SET FloatBal.AvlBalance = (FloatBal.AvlBalance - LoadBal.TotalLoadAmt)
FROM dbo.[EPP_Fin_FloatBalance] FloatBal
INNER JOIN (SELECT AllLoad.CardProduct,sum(LoadAmt) AS TotalLoadAmt
FROM [AllLoads] AllLoad
LEFT JOIN dbo.EPP_Fin_FloatBalance_Log FloatBalLog
ON FloatBalLog.ID = AllLoad.ID
AND FloatBalLog.CardProduct = AllLoad.CardProduct
WHERE FloatBalLog.ID IS NULL
AND AllLoad.CardProduct IN ('cp51','cp52','cc51','LP01','AP01','AP02')
GROUP BY AllLoad.CardProduct) LoadBal
ON LoadBal.[CardProduct] = FloatBal.[ProductCode]
WHERE FloatBal.ProductCode ('cp51','cp52','cc51','LP01','AP01','AP02')

2) -- Insert the updated rows from the all loads table in to the log table.
INSERT INTO dbo.EPP_Fin_FloatBalance_Log
SELECT AllLoad.ID,AllLoad.CardProduct,GETDATE(),LoadAmt,'update'
FROM [AllLoads] AllLoad
LEFT OUTER JOIN dbo.EPP_Fin_FloatBalance_Log FloatBalLog
ON FloatBalLog.ID = AllLoad.ID
AND FloatBalLog.CardProduct = AllLoad.CardProduct
WHERE
FloatBalLog.ID IS NULL
AND
AllLoad.CardProduct IN ('cp51','cp52','cc51','LP01','AP01','AP02')

I would like to perform these 2 tasks in a single query.

Kindly let me know the way to achieve this.

Thanks,
Sandesh

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-22 : 13:52:01

Do the update and add a trigger?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2012-02-23 : 12:06:38

Thank you for the reply. Earlier we were using triggers but could not solve the purpose. So came up with this 2 statements. Is there any other option apart from trigger?

Thanks,
Sandesh

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 12:11:37

you can use 2008's OUTPUT clause for this. It's called COMPOSABLE DML

Peso has a few nice posts on the subject.

Start reading here:
http://weblogs.sqlteam.com/peterl/archive/2009/04/08/Composable-DML.aspx

Example:


-- SQL Server 2008
INSERT      @Audit
            (
                        CustomerID,
                        oldValue,
                        newValue
            )
SELECT      CustomerID,
            oldValue,
            newValue
FROM        (
                        UPDATE      w
                        SET         w.Value = f.Value,
                                    w.LastEdited = GETDATE()
                        OUTPUT      deleted.CustomerID,
                                    deleted.Value AS oldValue,
                                    inserted.Value AS newValue
                        FROM        @Work AS w
                        INNER JOIN @File AS f ON f.CustomerID = w.CustomerID
            ) AS d
WHERE       oldValue <> newValue

SELECT      *
FROM        @Audit

SELECT      *
FROM        @Work

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2012-02-24 : 05:31:33

Thank you.

Thanks,
Sandesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources