-1

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:

enter image description here

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

Community
  • 1
  • 1
Mr. Boy
  • 60,845
  • 93
  • 320
  • 589
  • Yes it is possible, but where is the actual code that reads and the code that writes? – Steve Dec 20 '16 at 16:44
  • @Steve I'm doing this migration manually to populate local test data, not from code. (added this to the Q) – Mr. Boy Dec 20 '16 at 16:45

1 Answers1

2

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
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • I can't get this syntax to work, I'm not sure how to qualify the remote DB. I updated my question, can you edit the answer specific to my example? – Mr. Boy Dec 20 '16 at 17:05
  • You need to have a linked server created in order to access the data remotely like this. https://msdn.microsoft.com/en-us/library/ms188279.aspx – Sean Lange Dec 20 '16 at 17:08
  • Not come across that... perhaps you can expand an example in you answer. I'm assuming this must be done in a script coded explicitly, VS doesn't provide a GUI-friendly way? I'll play around with it in the meantime anyway :) – Mr. Boy Dec 20 '16 at 17:17
  • I did provide an example. Notice the 4 part naming? The first one is the linked server name. You need to create that. Or you can use the data migration tool (Tasks -> Import Data) in SSMS since it sounds like this is a one time thing. – Sean Lange Dec 20 '16 at 17:24
  • I meant a full example including the server linkage in the script. I seem to have this figured out now but only after digging through that link you shared and several false attempts... a future viewer would benefit from seeing that in your example. Also... Tasks -> Import Data sounds like it would be a valid alternative answer. – Mr. Boy Dec 20 '16 at 17:34
  • I couldn't begin to script out your linked server. There are so many options and details that any example would only be a partial example. That is why I provided the link to MS. And since you specifically asked about using a stored procedure and storing it in a table I only mentioned import data wizard as an afterthought. Glad you got it working. – Sean Lange Dec 20 '16 at 19:42
  • Well I say I got it working _except_ that my table has column `Category` which is an `IN` parameter to the stored proc, not something it returns. I can't see a way around that – Mr. Boy Dec 21 '16 at 13:58