Today I've found a helpful little piece of SQL I wasn't aware of so I share this with the whole Internet in the hope to help someone else. The scenario was the following, I have a remote server from which I need to import a few tables on my local SQL Server for further processing. The Two servers are connected through a VPN. The connection is good but I can’t kill the band with a long run series of data transferts through the network. So I first set a linked server from the local machine to the remote server that allows me to query the remote server on the Database I'm interested in. Then, to transfer the table I was interested in I wrote this code:
USE MyNewLocalDb
SELECT FIELD1, FIELD2, FIELD3 .... FIELDn INTO dbo.MynewTable FROM
[MyRemoteServer].[MyRemoteDb].[Dbo].[MyremoteTable]
In this case I had just a simple select from table, but If you need to build the table from a a Join, in the from clause you can write a complex select with joins or whatever you need. The Table is very raw, but then you can set PK, indexes and whatever else you need on it.
Obviously you need to have the proper rights on the two databases to make this work, you have to be at least Db_Owner of the destination db and Db_Datareader on the source db.