CODEWITHSUNDEEP
sql-serversql-server-2008
Newboy11
Newboy11

Reputation: 3136

Optimize update and insert query

I have this code:

string query = @"UPDATE [dbo].[TrippingTariffTransaction]
                 SET [IsActive] = 0
                 WHERE Id in (SELECT Id 
                              FROM [dbo].[TrippingTariffTransaction] 
                              WHERE Trip = @Trip 
                                AND TrippingTariffId = @TrippingTariffId);";
query += @"INSERT INTO [dbo].[TrippingTariffTransactionAuditTrail]
                            (LogDatetime, MasterlistId, ComputerName, TrippingTariffTransactionID, Activity)                       
               SELECT 
                   GETDATE(), @MasterlistId, @ComputerName, Id, @Activity 
               FROM 
                   [dbo].[TrippingTariffTransaction] 
               WHERE 
                   Trip = @Trip AND TrippingTariffId = @TrippingTariffId";

How can I optimize this code. I have redundant select. Thanks in advance :)

Upvotes: 0

Views: 136

Answers (2)

Squirrel
Squirrel

Reputation: 24763

You can make use of the OUTPUT clause

update  t
set     IsActive    = 0
OUTPUT  getdate(), @MasterlistId, @ComputerName, INSERTED.Id, @Activity
INTO    TrippingTariffTransactionAuditTrail 
        ( LogDatetime, MasterlistId, ComputerName, TrippingTariffTransactionID, Activity)
FROM    TrippingTariffTransaction t
WHERE   t.Trip             = @Trip 
AND     t.TrippingTariffId = @TrippingTariffId

Upvotes: 1

Amin Mozhgani
Amin Mozhgani

Reputation: 604

You can use temp table:

SELECT Id INTO #T
FROM [dbo].[TrippingTariffTransaction] 
WHERE Trip = @Trip 
AND TrippingTariffId = @TrippingTariffId

UPDATE [dbo].[TrippingTariffTransaction]
SET [IsActive] = 0
WHERE Id in (SELECT Id FROM #T)

INSERT INTO [dbo].[TrippingTariffTransactionAuditTrail]
(LogDatetime, MasterlistId, ComputerName, TrippingTariffTransactionID, Activity)                       

SELECT 
GETDATE(), @MasterlistId, @ComputerName, Id, @Activity 
FROM #T

DROP TABLE #T

Upvotes: 0

Related Questions