Speeding up insert query over 2 databases

Question

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

score 3 · Accepted Answer · answered Sep 21 '11 at 23:28

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).

score 1 · Answer 2 · answered Sep 21 '11 at 23:10

If you are regularly performing this kind of database to database transfer, you should probably look at DTS or SSIS (depending on which version of SQL Server you are using). Both technologies are specifically designed to extract, transform and load data between different sources and destinations.

score 1 · Answer 3 · answered Sep 22 '11 at 00:08

If all you need is to copy the data between tables with the same structure, this should work:

INSERT INTO Database2.dbo.Table2 SELECT * FROM Database1.dbo.Table1

If you need to transform the data as well (as your example seems to indicate), you may or may not be able to do it in a single statement, depending on the complexity of the transformation.

Speeding up insert query over 2 databases

3 Answers3

Linked