I have to use a piece of software that once it runs an analysis it creates a table with a unique reference id. To access these results via SQL is a pain, so I am creating a template so that users can easily pass the results through into Excel.
The problem I have is that the table name is constantly changing. Is there a way of making the:
Select * From <variable>
possible?
Thanks in advance
Yes, it is called Dynamic SQL:
SQL Server
DECLARE @sql AS VARCHAR(MAX)
SET @sql = 'Select * From ' + @yourTableNameVariable
EXEC(@sql)
ORACLE source
sql_stmt := 'Select * From ' || yourTableNameVariable;
EXECUTE IMMEDIATE sql_stmt;
MySQL source
SET @s = CONCAT('SELECT * FROM ',@yourTableNameVariable);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Disclaimer I am only proficient in SQL Server. The other examples are modifications of code found as defined in the [source] next to the DB engine's name.
It is possible with dynamic sql for example for t-sql it looks like
declare @tablename varchar(max),
@sql varchar(max)
set @tablename = 'tableName'
set @sql = 'Select * From '+@tablename
exec(@sql)
DECLARE @Table_Name NVARCHAR(max), @SQL NVARCHAR(max) SET @Table_Name = 'SELECT Result_ID FROM #TmpResult WHERE ResultsSetName = ''Company A''' SET @SQL = 'Select * From '+@Table_Name EXEC @Table_Name EXEC(@SQL)
SET @Table_Name = 'SELECT Result_ID FROM #TmpResult WHERE ResultsSetName = ''Company A'' ' you set whole select statement not table name. You should set here table name for example SET @Table_Name = 'Salary' or from query select @Table_Name = name from table ...(1 row(s) affected) Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'SELECT'.DECLARE @Table_Name NVARCHAR(max), @SQL NVARCHAR(max) SET @Table_Name = 'SELECT Result_ID FROM #TmpEventSet WHERE ResultsSetName = ''Company A'' ' SET @SQL = 'Select * From [Analysis].[dbo].'+@Table_Name EXEC(@SQL) EXEC(@Table_Name) Maybe I can't run two of these together as when I run them seperatly they work fine?SET @Table_Name = 'SELECT Result_ID FROM #TmpEventSet WHERE ResultsSetName = ''Company A'' ' doesn't return table name. After hits code SET @SQL = 'Select * From [Analysis].[dbo].'+@Table_Name the SQL value will have Select * From [Analysis].[dbo].SELECT Result_ID FROM #TmpEventSet WHERE ResultsSetName = ''Company A'' So it doesn't make sense.