我们如何使用大量数据集执行插入和更新操作...... [英] How can we perform the Insert and Update operations with large amount of data set ......

问题描述

嗨专家，

我的结果集有100万条记录。（结果集来自  select语句）。现在我想使用此结果集10000记录更新或插入目标表 一次。

i have a result set having 1 million records .(result set is coming from  select statement). Now i want to update or insert the target tables by using this result set 10000 records  at a time.

没有使用临时表 或表变量 或游标或公用表表达式（cte），有没有办法以最佳方式完成这项任务？

with out using temp table  or table variable  or cursor or common table expression(cte),is there is any way to achieve this task in optimal way?

提前致谢，

Ramesh Reddy Mallidi。 

Ramesh Reddy Mallidi. 

推荐答案

这是用于测试还是面试？

Is this for a test or job interview?

我想查看您查询的表格以获取结果集，然后查看要更新的表格。你能发布你的代码吗？我问的原因是你可以直接在给你结果集的同一查询中更新表。 

I would like to see what tables you queried to get the result set, and then what tables you want to update. Can you post your code please. The reason I ask is that you can probably update the tables directly in the same query that gave you the result set. 

下面是一个不太优雅的方法来更新表＃temp1 table＃temp2。它目前设置为一次只更新1行 - TOP 1.您可能会发现它很有用。执行一次后，＃temp1只会更新一行。如果再次执行UPDATE
，您将看到现在更新了两行。 

Below is a not so elegant way to update table #temp1 from table #temp2. It currently is setup to only update 1 row at a time - TOP 1. You might find it useful. After you excute it once, only one row from #temp1 is updated. If you execute just the UPDATE again, you will see that now two rows are updated. 

drop table tempdb..#temp1
drop table tempdb..#temp2
create table #temp1 (recid int identity, c1 varchar(5))
create table #temp2 (recid int identity, c2 varchar(5))

insert into #temp1 values('a1'),('b1'),('c1')
insert into #temp2 values('a2'),('b2'),('c2'),('e2')

alter table #temp1 add UPDInd int

-- put in while loop IF EXISTS (select top 1 1 from #temp1 where UpdInd is null)

    update t1 set t1.c1 = t2.c2, t1.UPDind = 1
	   --select t1.recid, t2.recid  
	    from #temp1 t1 inner join #temp2 t2
		  on t1.recid = t2.recid 
	   where t1.recid in 
		  (select top 1 t1b.recid from #temp2 t2b inner join #temp1 t1b 
				    on t2b.recid = t1b.recid
				    where t2b.recid  = t1b.recid and
					   t1b.updind is null) 
				    
select * from #temp1	

这篇关于我们如何使用大量数据集执行插入和更新操作......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！