Applications

SQL Server: Bulk Update

I maintain a rollup table of project metrics, which get updated on a frequent basis. This table contains a lot of calculations and data from multiple sources, which first populates a TABLE variable. Once all of the calculations and aggregate data sources have been completed, I want to update my project metrics table with the contents of the TABLE variable. Rather than looping through my table variable and updating my project metrics table one row at a time, I would like to perform a BULK UPDATE. Turns out to be a very simple SQL statement in which I join the table I am updating [dbo].[DeliveryDashboardProjectMetrics] with my TABLE variable @work in order update the associated columns:

 

    UPDATE 
        [dbo].[DeliveryDashboardProjectMetrics] 
    SET 
        [d].[ProjectId] = w.[ProjectId], 
        [d].[ProjectType] = w.[ProjectType], 
        [d].[Solution] = w.[Solution], 
        [d].[EstimatedStart] = w.[EstimatedStart], 
        [d].[EstimatedEnd] = w.[EstimatedEnd], 
        [d].[EstimatedHours] = w.[EstimatedHours], 
        [d].[EstimatedRevenue] = w.[EstimatedRevenue], 
        [d].[ClientRating] = w.[ClientRating], 
        [d].[FirstActivity] = w.[FirstActivity], 
        [d].[LastActivity] = w.[LastActivity], 
        [d].[ActualHours] = w.[ActualHours], 
        [d].[ActualRevenue] = w.[ActualRevenue], 
        [d].[NonbillHours] = w.[NonbillHours], 
        [d].[BillHours] = w.[BillHours], 
        [d].[AverageRate] = w.[AverageRate], 
        [d].[EffectiveRate] = w.[EffectiveRate] 
    FROM 
        [dbo].[DeliveryDashboardProjectMetrics] [d] 
        INNER JOIN @work w ON [d].ProjectId = w.ProjectId

 

 

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.