Select Into a table on server1 from server2 without using ‘Linked Servers’

Posted on

Question :

I am trying to get all the records from one table into another table but these tables (which are identical) are on separate servers.

Any help would be appreciated.


Answer :

If your goal is just a one time transfer, the quickest method would be to use SQL Server Import and Export Wizard (Right click on the database –
Tasks – Export Data)

And here is SQL Server Management Studio Express (64-bit) if you don’t have one already.

Assuming you meant INSERT INTO Server2.dbo.TableA Select FROM Server1.dbo.Table and not SELECT INTO Server2.dbo.TableA FROM Server1.dbo.Table

You can use OPENROWSET

This method is an alternative to accessing tables in a linked server
and is a one-time, ad hoc method of connecting and accessing remote
data by using OLE DB. For more frequent references to OLE DB data
sources, use linked servers instead. For more information, see Linked
Servers (Database Engine). The OPENROWSET function can be referenced
in the FROM clause of a query as if it were a table name. The
OPENROWSET function can also be referenced as the target table of an
INSERT, UPDATE, or DELETE statement, subject to the capabilities of
the OLE DB provider.

You may rule it out though because of whatever constraint you have that’s stopping you from using a linked server.

If that’s the case you’ll need to use an ETL tool like SSIS or write a process in a client tool that does it without INSERT INTO ... SELECT syntax.

Leave a Reply

Your email address will not be published. Required fields are marked *