November 6, 2014 at 4:28 am
I am struggling to understand some of the methods on the internet that say how to do this.
I have a production table with 400 million rows.
I have a staging table which has 48 million rows. This data is the same as the production data, except one column has a different value.
Create Table Production
(
Id Int Identity(1,1),
Code Varchar(20),
ReferenceSequence int
)
-- Staging Table
Create Table Staging
(
Code Varchar(20),
NewSequence int
)
I need to update the production table with the newSequence value from staging to replace the ReferenceSequence. I.e:
Update Production
Set ReferenceSequence = Staging.NewSequence
From Staging
where Production.Code = Staging.Code
However, updating 48 million rows at once will generate a lot of logging!
How can I do 1 million rows at a time, commit the changes then do the next million?
I've tried some of the examples on the following page http://mangalpardeshi.blogspot.co.uk/2013/07/different-methods-to-execute-large.html , but they look to just update the tables with the same values.
November 6, 2014 at 6:11 am
What about just iterating the update
declare @n int = 1000000;
declare @cnt int = 1;
while @cnt > 0
begin
Update top(@n) Production
Set ReferenceSequence = s.NewSequence
From Production p
join Staging s on p.Code = s.Code
and p.ReferenceSequence <> s.NewSequence;
set @cnt = @@ROWCOUNT;
end;
November 6, 2014 at 6:43 am
Thanks for the reply. It's funny how you spend ages on something, then when you write it out in a question on a forum, the answer comes to you.
I ended up doing something along these lines:
DECLARE @Start int = 1 -- Initial starting point of the range update.
DECLARE @End int = 100000 -- Initial End point of the range update.
DECLARE @Increment = 100000 -- Increment to update each time.
DECLARE @max-2 = 48000000 -- Total number of rows to update.
WHILE @End <= (@Max + @Increment)
BEGIN
BEGIN TRANSACTION
UPDATE Production
SET ReferenceSequence = NewSequence
FROM Staging
WHERE Staging.Code = Production.Code
AND NewSequence BETWEEN @Start and @End
-- Set the next batch to update.
SET @Start = @Start + @Increment
SET @End = @End + @Increment
COMMIT
END
November 6, 2014 at 7:29 am
🙂
“understanding a question is half an answer”
? Socrates
Most probably it will be no any activity updating Staging when the script is running. Otherwise it may end up with some Production rows being not the same as in Staging.
November 6, 2014 at 9:42 am
If possible, you want to specify contiguous clustered key ranges on the main/larger table to UPDATE. You only want to each affected block of that table one time.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply