本文介绍了如何在单个更新语句中在SQL Server中进行批量更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



我有近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.

推荐答案

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中进行批量更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-13 21:46