Insert rows returned by stored procedure into a different table - different databases and servers

Question

I have a local table in database Test like:

CREATE TABLE [SavedData]
(
    [RowID] INT NOT NULL , 
    [Category] INT NOT NULL , 
    [X] DATETIME NOT NULL, 
    [Y] DATETIME NOT NULL,
    PRIMARY KEY ([RowID])
)

I have a procedure in remote database LiveData on a different server like GetData(INT category) which returns multiple rows RowID, X, Y.

My table is being used to capture data for local testing so I'm wondering if I can do this in one step rather than go via clunky CSV files or something? e.g. Category and the results of EXEC GetData(Category) are inserted into SavedData using some cross-DB query/script.

I'm using Visual Studio 2013 for DB queries if at all possible. I'm doing this migration manually to populate local test data, not from code in my application.

Specifically, I have two DB connections in VS:

DB 'Test' is in my localdb, DB 'LiveData' is the SQL2014 one.

Answer 1

Yes since you already have a table that matches the result of your procedure you can do this quite easily.

insert SavedData
(
    Category
    , X
    , Y
)
exec MyRemoteServer.DatabaseName.SchemaName.ProcedureName