加快2个数据库的插入查询 [英] Speeding up insert query over 2 databases

问题描述

我有这个查询，下面我从1个数据库中获取某些列，然后将它们插入另一个数据库中的另一个表中.然后，我将删除要复制的表.目前，需要5分36秒才能复制5300多条记录.有什么办法可以提高速度?

I have this query below which I am getting certain columns from 1 database and I am then inserting them into another table in another database. I will then Delete the table I am copying from. At the moment it takes 5 minutes and 36 seconds to copy a bit over 5300 records. Is there any way I can improve the speed?

Declare @cursor cursor, @Firstname nchar(50), @MiddleInitial nchar(5), 
@Surname nchar(50), @EmailAddress nchar(100), @DOB nchar(8), @Sex char(1), @altEmail nchar(100)

set @cursor = cursor for select Firstname, MiddleInitial, Surname, HomeEmailAddress, 
DateOfBirth, Sex, WorkEmailAddress from cs_clients

open @cursor

fetch next from @cursor into @FirstName, @MiddleInitial, @Surname, @EmailAddress, @DOB, @Sex, @altEmail
while @@fetch_status = 0

begin
    set nocount on
    use hrwb_3_0
    declare @Password nvarchar(100), @EncryptedText nvarchar(100)
    exec L_Password_GetRandomPassword @Password output, @EncryptedText output

    declare @userID nvarchar(100)
    exec L_Password_GetRandomPassword @userID output, @EncryptedText output

    set nocount off

        set @EmailAddress = isnull(@EmailAddress, @altEmail)        

        insert into A_User values
        ('CS', 'CLUBSAIL', rtrim(@userID), rtrim(@Password), rtrim(@Surname), rtrim(@FirstName), rtrim(@MiddleInitial), 15, 'NA', 'NA', '', rtrim(@EmailAddress), rtrim(@DOB), 1, 0, 1, 0, '', rtrim(@Sex), '')

    fetch next from @cursor into @FirstName, @MiddleInitial, @Surname, @EmailAddress, @DOB, @Sex, @altEmail
end

推荐答案

速度很慢，因为您一次只能做一次.

It's slow because you are doing them one at a time.

请参见此处，了解一些一次执行多行的方法:

See here for some methods of doing multiple rows at once: http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/

或者在本地数据库上创建一个临时表，然后使用该表一次插入所有内容(即在一条语句中).

Or create a temporary table on the local database then use that to insert everything at once (i.e. in one statement).

这篇关于加快2个数据库的插入查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！