从另一个表插入表 [英] Insert into a Table from Another Table

问题描述

我在SQL Server 2008中有两个表。一个是tempTable（有28列），其中包含170万行。它还有一个名为RowID的标识列。其他是BaseTable（有52列）。现在我要将具有修改值的tempTable中的所有行插入到BaseTable中。我确实喜欢以下...

I''ve a two tables in SQL server 2008. One is tempTable(with 28 columns) which contains 1.7 million rows. It also has an identity column, named RowID . Other is BaseTable(with 52 columns). Now I''ve to insert all the rows from tempTable to BaseTable with modified value. I did like following ...

declare @i int
select @i=MAX(RowID) from tempTable
declare @a varchar(10), @b datetime, @c char(10), @d bit, @e int=0, ... and so on
while(@i<0)
BEGIN
select @a=Col1, @b=Col2, @c=Col3, @d=Col4, @e=Col5, ....so on ... from tempTable where RowId=@i
set @a += 'Hello'
set @b = datediff(...)
if(@e>10000) then
begin @d=1 end
set @e = cast(Col9 as int)*1.1
.
.
.
.
so on.......(thus I've to edit all the values)

insert into BaseTable(Col2, Col3, Col6, ......) values (@c, @a, @d, @b, @e, ........)
set @i -= 1
END

但需要很长时间插入...

有没有其他方法来更新tempTable&然后有效插入BaseTable ???

感谢提前...

But it takes long time for insertion ...
Is there any other way to update tempTable & then insert into BaseTable effectively ???
Thanks in Advance...

推荐答案

如果您只使用temTable进行临时存储，请尝试使用内存临时表 [ ^ 而不是tempTable。它可能会给你带来更好的性能。

If you are using temTable only for temporary storage, try using an in-memory temporary table[^] instead of tempTable. It might give you better performance.

看看使用INSERT和SELECT子查询插入行 [ ^ ]，它可以帮助你摆脱循环。同样，Abhinav建议尝试使用内存临时表而不是tempTable。

Take a look at Inserting Rows by Using INSERT and SELECT Subqueries[^], which should help you get rid of the loop. Also as Abhinav suggested try to use a in-memory temporary table instead of tempTable.

这篇关于从另一个表插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！