Post reply

Transfer data from sql server view to sql server table - fastest method?

  • September 24, 2009 at 4:34 am

    #210039

    Hi guys,

    i have a simple task where i want to transfer data from a sql server 2005 view to a sql server 2005 table. The view and table are on the same server.

    The sql for the view is not too complicated. It is a select statement with a few joins to other tables on the server. The big issue is that there are around 14 million records to transfer.

    I have created an ssis package which uses an ole db source object & an ole db destination object to transfer the data. This worked but took around an hour to complete the task.

    Does anyone know how to get the SSIS package to to run faster? Is there a different control/object i could use which improves the effeciency of a straight data transfer?

    Regards,

    Shuja

  • September 25, 2009 at 8:29 am

    #1058486

    What about using an execute sql task that contains the insert code, since the view and table are on the same server?

    The command would be of the form

    insert into (target server).(target table)

    (...)

    select ...

    from (source server.(source view)

  • September 25, 2009 at 10:22 am

    #1058558

    Is there any reason why you need SSIS for this task? If both of the objects are on the same server, you should be able to just run your insert statement in SSMS or a SQL Server Agent job without having to involve the SSIS engine at all, which should improve performance.

    Example:

    INSERT [db1].[dbo].[myDestinationTable] ([col1], [col2], [...])

    SELECT [col1], [col2], [...] FROM [db2].[dbo].[mySourceTable]

    hth,

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • September 25, 2009 at 3:42 pm

    #1058678

    Hi,

    The reason i need to use an SSIS package is that this process i want to run is part of a monthly download which is done through an application called data academy. Data academy is like a user friendly ssis package development tool although we feel it is not as effecient as using an ssis package.

    This is the reason i have been trying to re-create my download process package in SSIS.

    I will try the suggestions you guys have made and see which of these will be the fastest.

    Regards, Shuja

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply