2

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

Carlos80
  • 433
  • 15
  • 32

2 Answers2

3

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.

Community
  • 1
  • 1
Giannis Paraskevopoulos
  • 18,261
  • 1
  • 49
  • 69
1

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)
Robert
  • 25,425
  • 8
  • 67
  • 81
  • Hi Parado, Thank you very much for the quick reply. I'm trying to implement your answer but @Tablename is not working. Do you know if this is not possible in dynamic 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)` – Carlos80 Aug 04 '14 at 11:01
  • Here `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 ...` – Robert Aug 04 '14 at 11:06
  • Thanks Parado, I think it must be something to do with the quotations as I'm still getting an error `(1 row(s) affected) Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'SELECT'.` – Carlos80 Aug 04 '14 at 11:14
  • I've updated some table names but here's the whole thing `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? – Carlos80 Aug 04 '14 at 11:20
  • @Carlos80 This line `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. – Robert Aug 04 '14 at 11:22
  • Doh! I see this now, does this mean that what I'm trying to do is not possible? Essentially I have a temp table that concatenates two columns to create the newly created table name. I was hoping that I could select * from based on the concatenated value held in my temp table. – Carlos80 Aug 04 '14 at 11:36
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/58606/discussion-between-parado-and-carlos80). – Robert Aug 04 '14 at 11:38