Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks

share|improve this question
    
Which RDBMS are you using? –  Thomas Stringer Feb 1 '13 at 21:09
    
SQL Server 2010 –  Ross Barbish Feb 1 '13 at 21:29
2  
There is no 2010. You mean 2008R2? –  Thomas Stringer Feb 1 '13 at 21:30
2  
What is the goal of avoiding linked servers? This is typically how you set up communication between servers (hence the name). –  Aaron Bertrand Feb 1 '13 at 21:36
2  
@RossBarbish adding your last comment to the question would make it a more sound question and indicates the goal. –  Shawn Melton Feb 2 '13 at 3:40
show 2 more comments

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.