1

I would like to ETL a table from one instance of SQL Server to another instance of SQL Server. I do not want to create an SSIS package. I have tried

  1. to extract the data using an External Table on the destination (i.e. I use the External Table to create a local copy e.g. with SELECT INTO #Hello FROM MyExternalTable).
  2. Linked Server with using SELECT * INTO #HELLO FROM MyLinkedServer.MyDB.dbo.MyTable
  3. I have also used OPENROWSET.

They all run single threaded, which I can see from the execution plans. I have not used bcp but it seems that I first need to save the table to a file and then read it from the file and load it. So I do not think it is the correct tool for this task. What is the fastest approch to copy a table from one SQL Server instance to another SQL Server instance?

xhr489
  • 827
  • 11
  • 30

1 Answers1

1

As determined in the comments, since you don't need to apply any transformations, this is essentially just a table copy, then you may want to consider using Replication, especially for just a single table.

I'd recommend looking into one-directional Transactional Replication. It is available even on Standard Edition, and will keep the data up to date in near-realtime.

While the initial snapshot synchronization may take a while for a ~30 GB table, this may not even be the fastest methodology for copying the table once. But because it'll keep the data in sync as it changes, it may save you from having to do a full copy of the table again in the future. So it is arguably the most efficient solution in the long term.

J.D.
  • 40,776
  • 12
  • 62
  • 141