-1

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?

1

1 Answer 1

0

In pseudocode - the actual syntax left to the reader:

DECLARE @myvar MYTYPE = (SELECT alias.col FROM schema.localtable WHERE col2 = @parm)

DECLARE @nsql NVARCHAR(4000) -- because this query is short

SET @nsql = 'SELECT alias.col1, alias.col2 
  FROM db.schema.table alias
  WHERE alias.IndexedCol = ' + @myvar + ';'

SELECT oqInstanceB.col1, oqInstanceB.col2 FROM OPENQUERY([mylinkedserverInstanceB],@nsql) oqInstanceB

WARNING WARNING WARNING

This is concatenated SQL,which is vulnerable to SQL Injection. In the formulation above, @parm is passed into the SP, and is not vulnerable in and of itself, however, @myvar is vulnerable to second order SQL injection - if @myvar is something like 'a''; DROP DATABASE xyz;--' for example, you might be in a world of hurt when that executes on ServerB.

You'll obviously need a linked server to ServerB first.

Note the "pull" through OPENQUERY with an explicit equality condition in the WHERE clause; this will execute exactly as written on ServerB.


Alternately, if you are on a version of SQL Server that supports it (which you probably are) you can try EXECUTE AT - from Microsoft docs:

EXECUTE ('SELECT ProductID, Name   
    FROM AdventureWorks2012.Production.Product  
    WHERE ProductID = ? ', 952) AT SeattleSales;

Where SeattleSales is a linked server name. Note that the parameter is no longer being concatenated; now it's a parm.


Always beware performance using linked servers.

Never use four part naming (instance.database.schema.table) - always use EXECUTE AT or OPENQUERY or another technique that sends an entire query - including all applicable JOIN and WHERE conditions - to the remote instance.

3
  • Better options: instead of EXECUTE AT you can do EXEC LinkedServer.YourDb.sys.sp_executesql N'SELECT ... WHERE ProductID = @id', N'@id int', @id = @id;. Alternatively just do a normal linked server query, without any dynamic SQL SELECT * FROM LinkedServer.YourDb.dbo.SomeTable ... Nov 22, 2022 at 15:47
  • One other option is to create an EXTERNAL TABLE using Polybase which allows you to treat data from Server B like it's stored locally on Server A. There are some nuances to this over linked servers, but one nice benefit is you manage security locally rather than having to limit access to an entire Linked Server connection or potentially add additional permissions to Server B. Nov 22, 2022 at 20:42
  • Sorry for being a bit on the slow side of things. I'm really trying to get acclimated at this as I have a passion for SQL and this is my first DBA- role. I'm having a bit of trouble still and wanted to add to the thread.
    – user264428
    Nov 30, 2022 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.