Trying to use dynamic SQL to join column names in SQL Server 2008
source link: https://www.codesd.com/item/trying-to-use-dynamic-sql-to-join-column-names-in-sql-server-2008.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Trying to use dynamic SQL to join column names in SQL Server 2008
Using SQL Server 2008, I would like to specify the column names of an inner join using dynamic SQL. The two tables I am joining have the same names for the columns I am joining on. I know SQL Server does not support natural joins; if it did, the dynamic SQL would look like something like this:
DECLARE @join_columns AS NVARCHAR(100)
DECLARE @sql_1 AS NVARCHAR(4000)
SET @join_columns = 'Age, Gender'
SET @sql_1 = '
SELECT ' + @join_columns + ', table_1.Field_x , table_2.Field_y
FROM table_1 , table_2
NATURAL JOIN ON ' + @join_columns
EXECUTE sp_executesql @sql_1
Now, I realize this won't work because there are no natural joins in SQL Server. So, what is the next best way to do this?
Here are a few things I unsuccessfully pursued:
Tokenizing
@join_columns
and forming up a dynamicWHERE table_1.<col_1> = table_2.<col_1> [AND...]
kind of clause. But, it doesn't look like T-SQL has string tokenization functions.Using dynamic SQL to make temp tables, each with a new key column called
temp_key
that is the concatenation of the fields in@join_column
. If it were easy to dynamically concatenate these, then the final join could be always beON #temp_table_1.temp_key = #temp_table_2.temp_key
. One way of setting this up would be to use theREPLACE
function to replace the commas in@join_column
with plus signs. The problem I ran into here was that the concatenation required casting for the non-VARCHAR
columns. So, I'd have to know column types ahead of time - back to square one.
Ideally, I'd like to keep @join_columns
as a comma-delimited string, because I am using it elsewhere in dynamic SQL GROUP BY
clauses.
It may be that one of the failed approaches above could work, using something I missed. Or, maybe there's a better overall approach.
Any suggestions?
Update
Solution was a combination of both @usr and @Karl's posts below. I used @usr's suggestion to track down a tokenizing table-valued UDF (ended up going with this one ). Then I used @Karl's COALESCE
example to turn the resulting table into the WHERE
clause. I also used @Karl's full example for another join problem I just ran into. I wish I could give answer status to both posters - thanks guys!
I find that this works well:
declare @whereClause varchar(8000)
declare @table2 varchar(255)
declare @table1 varchar(255)
set @table1='SomeTable'
set @table2 = 'SomeOtherTable'
SELECT COLUMN_NAME as [joincolumn]
into #join_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table1
AND COLUMN_NAME not in ('list names of columns that are not to be joined on here')
select @whereClause=coalesce(@whereClause+' and ','')+
'['+@table2+'].'+joincolumn+'=['+@table1+'].'+joincolumn +'
'
from #join_columns
print @whereClause
You can then create a dynamic SQL script and tag the WHERE clause to the back of it
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK