如何在单个更新语句中在SQL Server中进行批量更新 [英] How to do bulk update in SQL server in single update statement

问题描述

我有近1M条记录，我需要根据每个ID更新公司名称。

如何在sql中的单个更新语句中更新它服务器。

总之我想表演



更新tblname设置

Name ='updatedname'（这将是100的）

其中

cid in

（

100s

）



谢谢，

Nilesh



什么我试过了：



一个接一个地做，但因为它有1M条记录，所以对于1M记录来说这将是非常冗长和耗时的。

Hi,
I have almost 1M records in which I need to update its Company name against each ID.
How I can update it in single update statement in sql server.
In short I would like to perform

update tblname set
Name='updatedname'(which would be 100s of)
where
cid in
(
100s of
)

Thanks,
Nilesh

What I have tried:

Doing it one by one but as it has 1M records it will be very lengthy and time consuming for 1M records.

推荐答案

我创建了一些超过1m记录的虚拟数据

I created some dummy data with over 1m records

Create table company
(
	Id int identity(1,1),
	[Name] nvarchar(50),
	Address1 nvarchar(50),
	Address2 nvarchar(50),
	Address3 nvarchar(50),
	ZipPostCode nvarchar(20),
	Country nvarchar(50)
	-- and whatever else you need
)
GO
insert into company ([Name]) values ('dummy')
GO 1000
go
INSERT INTO company ([Name]) SELECT [Name] FROM company
go 6

I then created a temporary table with one record for each of the records in company, just the Id and the current name

select Id, [Name] into #temp from Company

我使用新名称更新了临时表 - 相当快，只有2列，例如

I updated the temporary table with the new name(s) - reasonably quick as only 2 columns e.g.

update #temp set [Name] = [Name] + cast(Id as varchar)

要更新原始版本，请使用连接 - 它更快

To update the original use a join - it's faster

update A
set [Name] = T.[Name]
FROM company A
INNER JOIN #temp T on A.Id = T.Id

我的笔记本电脑花了大约2分钟来处理140万而没有大量可用内存。我希望设置一半的服务器能够占用一半的时间

It took my laptop around 2 minutes to process 1.4 million with not a lot of memory available. I'd expect a half decent server set up to take half that time

这篇关于如何在单个更新语句中在SQL Server中进行批量更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！