I have a query that grabs data from a .csv and inserts into a table I created, now I need to take a single column from my destination table and use it to gather data from another database from another server entirely before returning all the results to the table I created on the first server. Here's a basic sketch of what I mean:
My table and script currently exist on server A:: Let's say server A has five columns- ID, Name, Phone number, Business.ID, Trunk.ID
- I have a script that gives me ID, Name, Phone Number, and Trunk.ID, but Business.ID is actually only in Server B.
- I can find Business ID by using Trunk.ID, since Trunk.ID exists in server B.
I have a stored procedure that will find me every single business ID associated with a Trunk.ID on Server B.
- I want to enclose this stored procedure in my script running on server A to basically do this: -Grab the trunkID from server A and gather all the business IDs associated with it on server B -Copy the business IDs from server B and place them in the table on server A when the query is run.
Update: Just wanted to say thank you to everyone that replied and hopefully provide an update.
Table A on Server A Contains an ID value that matches to an Value present on Table B on Server B.
I've named a new variable called Tvar: declare @tvar varchar(xxx); Select @tvar=ID from dbo.tableA;
If I want this script to automatically take this variable (ID from table A) and pull the matching data from table B. Would it make more sense to simply include a join in my script to define the column in my table.
So, walking back to the beginning of the script, maybe I could define my ID row as the result of a check using this variable?
field1=exec(select ID.B as BusinessID from databaseB.SchemaB.ColumnB where ID.A = ID.B
Does this make any sense at all?