Updating multiple tables in sql server 2016
Taught by Andy Leonard, published author, Data Philosopher, and Biml Hero from Farmville, Virginia. Drew Furgiuele’s done automation for over a decade, and he’ll teach you Power Shell in two days of hands-on labs.You’ve been managing SQL on Windows, but now, you want to install, configure, and troubleshoot SQL Server on Linux.Anyhow, here's my solution with a cursor, and im wondering if there's a way of doing this better? Customer Id FROM Advert AS T1 INNER JOIN Campaign AS T2 on T1. So pretty much, is there a better way of doing this, and if so, how? Either use CURRENT_OF or throw them all into variables.
You can consolidate this to look like: UPDATE Cached Stats SET /* Basically we are going to set the counts based on the type inline in the update clause*/ Leads= CASE WHEN (@Type = 1 OR @Type = 4 OR @Type=3 ) THEN Leads 1 ELSE LEADS END, Clicks=CASE WHEN (@Type=0) THEN Clicks 1 ELSE Clicks END, Views=CASE WHEN (@Type=4) THEN Views 1 ELSE Views END, Publisher Earning = @Publisher Earning Publisher Earning, Advertiser Cost = @Advertiser Cost Advertiser Cost, FROM Cached Stats CS INNER JOIN Inserted INS ON CS.
My insert trigger (written to work with a single record) then graps the data off the Inserted table, and sends it off to the stored procedure. Publisher Customer Id AND CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date]) AND s. Advert Id AND s.[Type] = 2 ), Leads = Leads ( SELECT COUNT(*) FROM Inserted s WHERE s. Publisher Earning ISNULL(( SELECT SUM(Publisher Earning) FROM Inserted s WHERE s. EDIT: Below is the code that we finally got working. That could very well be a simpler approach than your triggers, depending on what the report query looks like. Also, in your trigger, you should try to write your updates to the materialized results table as a set based operation, not a cursor.
The delete trigger works in the same way, and (obviously? Publisher Customer Id AND CONVERT(Date, s.[Date]) = CONVERT(Date, i.[Date]) AND s. Advert Id AND s.[Type] = 2 ), Unique Views = Unique Views ( SELECT COUNT(*) FROM Inserted s WHERE s. Execution time for the whole operation went down from "virtually forever" (for the original solution) to something under one second, according to the OP's feedback. CREATE TRIGGER [dbo].[TR_STAT_INSERT] ON [iqdev].[dbo].[Stat] AFTER INSERT AS BEGIN SET NOCOUNT ON -- insert all missing "Cached Stats" rows INSERT INTO Cached Stats ([Date], Advert Id, Customer Id, Campaign Id, Campaign Name) SELECT DISTINCT CONVERT(Date, i.[Date]), i. Writing a cursor based trigger could potentially just be moving your problem from the report query to your table inserts instead.
This means statement-level information is unavailable.
You need to speed up a SQL Server app, but you only want to make index changes.