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.
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.