Joining or Union Dynamic tables 1

[tshad]

I am trying to write a stored procedure that joins a table or does unions that has the table name in another table:

ie, CREATE TABLE tables
(
@table varchar,
@foreignKey varchar
@data varchar
)

From here I would want to do something like:

SELECT *
FROM table1
union
SELECT *
FROM table2
union *
SELECT *
FROM table3

where the table1, table2 and table3 come from the tables table.

Thanks,

Tom

 

[markros]

Assuming that all the tables have the same structure, try

declare @SQL nvarchar(max)
select @SQL = coalesce(@SQL + ' 
UNION ALL 
','') + '
SELECT * from ' + quotename(Table) from Tables

print @SQL -- to see if the sql is correct

execute (@SQL) -- to actually run the statement

PluralSight Learning Library

 

[tshad]

That was similar to what I had come up with but I like your approach better. I had never seen the QuoteName function before and it works pretty well.

What I am trying to do is try to return the values in certain reference tables. I don't know what the name of the tables are - this is dynamic. The "Tables" table have a list of reference tables in them that I can use for dropdowns in my application. The record would have the 4 columns, PK, Table name, name of the PK in the table, and the name of the column that has the text that we want to show in the dropdown.

So what I wanted to do is call a stored procedure that will send back the records from all the tables that are in the "Tables" table.

I had come up with:

********************************************
DECLARE @sql varchar(8000)

SELECT @sql = ' SELECT ''' + ForeignKeyTable + ''' AS TableName, '
+ ForeignKeyField + ' AS Value , '
+ ForeignKeyValueField + ' AS Text'
+ ' FROM ' + ForeignKeyTable
+ ' Order By TableName, Value'
FROM Tables

SELECT @sql

EXEC (@sql)
*************************************************

I was trying to see if there was a way to do this without using cursors or dynamic SQL.

Yours is similar but you use the Coalesce and QuoteName which I like better. So I changed it to this which works pretty well.
*******************************************
declare @SQL nvarchar(max)

select @SQL = coalesce(@SQL + ' UNION ALL ','')
+ 'SELECT ''' + QUOTENAME(ForeignKeyTable)
+ ''', ' + QUOTENAME(ForeignKeyField)+ ', '
+ QUOTENAME(ForeignKeyValueField)
+ ' from ' + quotename(ForeignKeyTable)
from Tables
***********************************************

What I am not sure about is that the main difference between yours and mine on the table and column names is that you send them to the QuoteName function.

What does that do for you that just using the name doesn't?

Thanks,

Tom

 

[markros]

Using QUOTENAME function without second parameter puts the column name (or table name) in brackets []. This is a safe measure for columns/table names with spaces in the name or other invalid characters. If you're 100% sure all your table names/column names don't have spaces or some other invalid characters, then using quotename is not required, but it's a better practice.

So, every time I write dynamic SQL, I automatically now use this function.

PluralSight Learning Library

 

[tshad]

Good to know.

Thanks,

Tom