Fastest Way to Update Rows in a Large Table in SQL Server

Question

Fastest Way to Update Rows in a Large Table in SQL Server

srinudw88

SSC Veteran

Points: 298
More actions
September 17, 2018 at 4:17 am

#408458

HI ,I have one doubt in sql server
Basically I need to run this on a table with 60 million rows, updating every row at once will crash, so I want to batch the query so that if it crash, it can re-run the query and it would skip the finished batch and just continue with the ones left over.
I want updated emp table data with refernce of empref table.here both table have millons of records ,but i am providign some sample data in the below.
while update time we need to splits batches with top clause and should not repeat existing top.
sample tables with data:
CREATE TABLE [dbo].[emp](
    [id] [int] NULL,
    [name] [varchar](50) NULL,
    [sal] [int] NULL
)
CREATE TABLE [dbo].[empref](
    [id] [int] NULL,
    [name] [varchar](50) NULL,
    [sal] [int] NULL
)
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (1, N'a', 100)
GO
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (2, N'b', 200)
GO
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (4, N'u', 300)
GO
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (7, N'x', 400)
GO
INSERT [dbo].[emp] ([id], [name], [sal]) VALUES (8, N't', 500)
GO
INSERT [dbo].[empref] ([id], [name], [sal]) VALUES (1, N'xx', 100)
GO
INSERT [dbo].[empref] ([id], [name], [sal]) VALUES (2, N'bb', 200)
GO
INSERT [dbo].[empref] ([id], [name], [sal]) VALUES (4, N'uu', 300)
GO
INSERT [dbo].[empref] ([id], [name], [sal]) VALUES (7, N'xxx', 400)
GO
INSERT [dbo].[empref] ([id], [name], [sal]) VALUES (8, N'tt', 500)
GO
INSERT [dbo].[empref] ([id], [name], [sal]) VALUES (10, N'ub', 600)
GO
based on above data I want output like below :
emp:
id    |name    |sal
1    |xx    |100
2    |bb    |200
4    |uu    |300
7    |xxx    |400
8    |tt    |500
--------------
I tried like below :
declare @i int
set @i=1
while ( @i <= (select max(id) from emp ))
begin
update TOP(2) t set t.name=s.name
from emp t join empref s on t.id=s.id
print @@rowcount
set @i=@i+1
end
above update query is updated only that top 2 records only and its not getting next top 2 records update query is not itterated .
its alway updated only that top 2 records only.
please tell me how to create multiple batches using while loop in sql server .

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1


select * from emp;

declare @offset int = 1, @fetch int = 2
declare @t table(id int primary key);
while @offset<(select count(*) from dbo.emp)
begin

insert @t
select id
from emp
order by id
offset @offset rows
fetch next @fetch rows only;
update e
set e.name=r.name
from emp e
join empref r on e.id=r.id
join @t t on e.id = t. id;
delete @t;
set @offset += @fetch;
end
select * from emp;
drop table emp;
drop table empref;
go

drew.allen SSC Guru Points: 76997 More actions · Answer 2

I think the following is going to be faster and simpler. It only reads the table once each loop instead of once to insert into the temp table and once to do the actual update.

declare @i int set @i=1

while ( @i = 1)
begin
    update TOP(2) t set t.name=s.name
    from emp t join empref s on t.id=s.id
    WHERE t.name <> s.name /* TEST WHETHER IT'S ALREADY BEEN UPDATED */
    print @@rowcount

set @i=SIGN(@@ROWCOUNT)
end

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA