Copying data between database tables

Hi. I need to move data from one database table to

another across database instances. A simple example of the typical

move would be:

[CODE]

INSERT into destination_db.dbo.table1

SELECT column1, column2, column3, column4 from source_db.dbo.table2

[/CODE]

My options are:

1. Create an SSIS package to perform the move.

2. Create sprocs and schedule the data move as jobs.

3. Write .NET code using sprocs to perform the move.

I'll have to move hundreds of thousands of records, so I want the

option that provides the best performance. I'm guessing that option 3

will be the slowest.

Thanks for the help!
To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.|||

BSHOE wrote:

To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.

BSHOE,

That won't work if the tables are on different instances. Which in this case they are.

To the original poster - if pure performance is your consideration then SSIS is the way to go.

-Jamie

|||

Jamie Thomson wrote:

BSHOE wrote:

To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.

BSHOE,

That won't work if the tables are on different instances. Which in this case they are.

To the original poster - if pure performance is your consideration then SSIS is the way to go.

-Jamie

I concur with Jamie. Write a simple SSIS job to extract and then load the data. I can pull 100,000 records a second from SQL Server 2005 on my laptop into a flat file. Look on my website for details on how I do this and just substitute a table for the flat file in my demo.