Is it possible to copy the data in a remote MSSQL table to a local Table?

Question

What is the best way to go about doing this? I have found some INSERT statements, but they all seem to work just for inner-database copying.

My rendition:

INSERT INTO [PROGRAM003].[dbo].[faq]
    ([id], [category], [question], [answer], [tags])
SELECT ([id], [category], [question], [answer], [tags])
FROM [SQL2005_552664_gsow].[dbo].[faq]

I am not very fluent with MSSQL yet, so any and all help is appreciated.

Answer 1

You might want to look into DTS or (2005+) Integration Services. Both of these will take data from one DB (Table) and copy the data into another DB (Table).

Link to Integration Services: http://msdn.microsoft.com/en-us/library/ms141091.aspx

Answer 2

You can use OPENDATASOURCE or OPENROWSET to access tables on other servers. You'll need to provide SQL credentials in the query or have both databases setup to allow access to your windows id for this to work. You can also use the import/export wizard in SQL Server Management Studio -- right click on the database and choose Tasks-->Import or Tasks-->Export.

Answer 3

How "remote" are we talking? If it's another database on the same server you can just prepend the database name, as your initial example showed:

INSERT INTO [mytable] SELECT * FROM [database].dbo.[table]

If it's on a completely different server, you need to set them up as linked servers. Then you can do this:

INSERT INTO [mytable] SELECT * FROM [server].[database].dbo.[table]

You must, of course, fully specificy the database and schema within that server as well, and expect performance to suffer because it will need to move data across the network.