Create a view in one server from a query to a different server

I'm using SQL Server 2005. I have a query to a server that gives a huge result set and I'd like to save that in a view on my local server.

Is there a way to either create a v开发者_Go百科iew from the server to my local server database directly, or export the result set locally and import it into my local server's database.

Thanks!

You could use OPENDATASOURCE or a linked server query from your local server to the remote server. That would allow you to create an object such as a temporary table locally and populate it with data from the remote server.

There is plenty of information on implementing this in Microsoft's online documentation.

Creating a view on the local server will not help you much, as physic data is stored on the remote server.

You can create a table on the local server from the view and use it instead.

Its a two step process: first you'll want to make a linked server. Then you'll need to create a view of a select statement on that.

If your data is currently on Server A, you'll need to make a linked server on Server B, referencing A.

Then (assuming your using SSMS), you can browse to that table, right click and chose select. This will show you exactly how to refernce the table. Then add a "create view [viewname] as" to the top and execute.