0

I'm working on creating some stored procedures to automatically mirror data from multiple servers/databases (40+) into one central server. I have created a table that has the column names from the databases that I am referencing like this:

Reference Table

What I'm wanting to do, is essentially grab the COUNT(ColumnID) based on the @TableID table variable that I declare. From there, the central server will already have corresponding columns for each of these sets of columns and the reference table also has the name of the central databases columns for each of these columns listed in the same table for the TableID and ColumnID. I want to pull these column names into an array and/or string where I can EXEC a dynamic Query such as EXEC('SELECT '+@ColumnsString+' FROM [LinkedServer].'+@TableName+'');

I already have the dynamic Linked Server's setup and working. But I'm looking for a way to either store the multiple column names into a single array that I can reference to create a string variable and also to reference for UPDATE queries. IE: SET @ColumnString = (@arrayvalue[1]','+@arrayvalue[2]+','+@arrayvalue[n]+'');

Is there a function available in SQL Server that could accomplish this?

OM Asphyxiate
  • 329
  • 1
  • 5
  • 14
  • Instead of an array, why not use a cursor with your `EXEC('SELECT '+@ColumnsString+' FROM [LinkedServer].'+@TableName+'')` – S3S May 23 '17 at 21:12
  • SQL Server does not have arrays however whenever I need to use one for dynamic SQL then I use a table variable and loop through it. – Andrew O'Brien May 23 '17 at 21:13
  • I doubt you need a cursor here but I can't totally wrap my head around what you are trying to do. – Sean Lange May 23 '17 at 21:14
  • @SeanLange I'm primarily making update/insert queries and want to dynamically pull the number of columns from the reference table and the names since some of the tables I'm referencing have more columns than I'm pulling data for into the central server. And if possible, using the reference table to create the UPDATE queries where I can essentially say `SET @CentralColumn = @LinkedServerColumn` since a lot of this data is not static and changes on the Live database and I want to be able to reflect that in the central server. This is while using the `EXCEPT` function between the two tables. – OM Asphyxiate May 23 '17 at 21:20
  • Why did you create a table the duplicates the functionality of SQL Server's system tables? It sounds like you want to create a dynamic SELECT query of all columns in a given table(s). That should be fairly simple to implement utilizing sys.columns, sys.tables, and if necessary sys.schemas. – Anthony Hancock May 23 '17 at 22:43
  • This is my first time working with linked servers, but the Linked Servers are all SQL Anywhere servers/databases. – OM Asphyxiate May 24 '17 at 13:36
  • So are you wanting to generate a dynamic query for every table that you are tracking in the table above? Easy enough to do but one piece you mentioned is the link server. Is that also in your table? Or is the link server always the same and variable portion is just the table name? What about the remote database name? That isn't shown anywhere here. – Sean Lange May 24 '17 at 15:34
  • @SeanLange I have multiple Linked servers but they are referenced from another table. I have already built out that portion of the script and have it in a loop for each server. Each of the Linked servers have the exact same database table structures but are just for individual locations with their own unique data. I'm must not sure how to handle multiple column variables that could vary in amount, etc. – OM Asphyxiate May 25 '17 at 13:50
  • It is not at all clear to me what you are having a hard time with here. Maybe it is just being able to generate a comma delimited list of the column names? You can use FOR XML like this. https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 – Sean Lange May 25 '17 at 14:19

0 Answers0