SQL Server: Using Data From Another Instance To Drive Logic In Current Instance (Using Linked Server)

Data is in another DB/Instance – but logic needs to be in this DB/instance

Your logic in the current database/instance sometimes is driven by data in a completely separate SQL Server instance.

People do this using different methods

• Use LinkedServer to bring over and join data
• Use SSIS or another mechanism to copy the data over
• Work outside SQL Server (like .Net, PowerShell, SSIS etc.,) to work against two separate instances
• …[other creative solutions]

I personally prefer PowerShell these days but this is more straight-forward without any frills.

Linked Server to the rescue

In this post, we are going to use a method that brings the data over from another source instance on the fly, saves it in a Temp table, works with it and cleans up. It uses a Linked Server connection. The post is more about using the data rather than setting up a Linked Server, so, I will assume that you have a Linked Server setup to the other instance whose data you will use to drive logic in the current instance.

Goal

In this specific example, I need to add a column named ExecutionId to a set of tables on “SQL Server Instance A”. The table names to which the column should be added are stored in a table named “[MetaData].[Tables]” on another instance – “SQL Server Instance B”. I need to loop through each of rows of [MetaData].[Tables] and add the column named ExecutionId to each table on the current database.

Disclaimer: In this case, the number of rows that need to be worked on was minimal. Please do not use this method for large datasets!

The pseudo code is:

• Connect to source instance and bring over the table list into a new table
• Populate list of tables to work with into a temp table
• Loop through the temp table and do column addition operation on each table
• Cleanup temp table
• Cleanup the table that held the data from the other server

When done incorrectly, you will run into a lot of errors. However, using the following self-explanatory 😉 code, doing so is quite easy. Notice that I stayed away from hard-coding the source instance and database name as part of the SQL so that I can easily change it if needed. This is also an example how to loop through rows without using a cursor.

---------------------------------
--Add column ExecutionId to our tables
---------------------------------
--Need to query in this form
--SELECT * FROM [MyProdServer\ProdInst].[MySourceDatabaseName].MetaData.Tables;
DECLARE @MetaDataSourceServer VARCHAR(125) = 'MyProdServer\ProdInst'
DECLARE @MetaDataSourceDB VARCHAR(125) = 'MySourceDatabaseName'
DECLARE @TableID INT;
DECLARE @SQL NVARCHAR(512) = 'IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ''MetaData_Tables'')
				       SELECT TOP 1000 TableID, SchemaName, TableName
				       INTO MetaData_Tables
				       FROM  '
					 + Quotename(@MetaDataSourceServer) + '.'
					 + Quotename(@MetaDataSourceDB) + '.'
					 + 'MetaData.Tables'; 

-- Create a permanent table with list of MetaData.Tables
BEGIN try
    PRINT 'Start: ' + Cast(Getdate() AS VARCHAR); 

    PRINT @SQL; 

    EXEC sp_executesql
      @SQL; 

    PRINT 'End: ' + Cast(Getdate() AS VARCHAR);
END try
BEGIN catch
    --Print-out and ignore if the error relates to table already being there. Should handle specific error though!
    PRINT Error_message();
END catch; 

--Create a temp table to loop thru
SELECT tableid,
       schemaname,
       tablename
INTO   #controltable
FROM   MetaData_Tables cmt
WHERE  NOT EXISTS (SELECT object_id
                   FROM   sys.columns c
                   WHERE  NAME = 'ExecutionId'
                          AND Object_id(Quotename(Db_name()) + '.'
                                        + Quotename(cmt.schemaname) + '.'
                                        + Quotename(cmt.tablename)) =
                              c.object_id); 

WHILE EXISTS (SELECT *
              FROM   #controltable)
  BEGIN
      SELECT TOP 1 @TableID = tableid,
                   @SQL = 'alter table ' + Quotename(schemaname) + '.'
                          + Quotename(tablename)
                          + ' add [ExecutionId] BIGINT NULL '
      FROM   #controltable
      ORDER  BY tableid ASC; 

      BEGIN try
          PRINT 'Start: ' + Cast(Getdate() AS VARCHAR); 

          -- Go ahead and execute the SQL that adds the column
          PRINT @SQL; 

          EXEC Sp_executesql
            @SQL; 

          PRINT 'End: ' + Cast(Getdate() AS VARCHAR);
      END try 

      BEGIN catch
          --We need to keep going instead of giving up on first error! Re-runs will handle errors.
          PRINT 'Error occured:'; 

          PRINT Error_message();
      END catch; 

      DELETE #controltable
      WHERE  tableid = @TableID;
  END 

DROP TABLE #controltable; 

DROP TABLE MetaData_Tables;

It is re-runnable!

Although not very cleanly implemented, notice that the code is re-runnable. i.e., if this fails mid-way for whatever reason, the next run (when scheduled from SQL Agent) would run again. The possible failure conditions are that the stage table is left-over, only some tables had the column ExecutionId added, etc.