how to get data from dynamic table in sql

Question

How can I get data from chosen table of my database? I'm going to work with database in c# application and I have the database includes that tables:

MyTable1;
MyTable2;
...

And I have tbl variable that is equal to tbl = "MyTable2";. I want to execute the code as following:select * from tbl

I try to execute this code:

SELECT   *
FROM     (
          SELECT TABLE_NAME
          FROM   information_schema.tables
          WHERE TABLE_NAME = 'MyTable1'
          );

But the code returned error that Every derived table must have its own alias

I want to get all data from table whose name is equal to my variable (tbl) and its value can also be changed. How can I do it?

@ADyson That won't do the job, because dynamic SQL is needed to build a query this way. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 22, 2019 at 16:18

Tim Biegeleisen · Accepted Answer · 2019-04-22 16:20:47Z

1

You might be able to do this using a prepared statement in MySQL:

SELECT TABLE_NAME
INTO @table
FROM information_schema.tables
WHERE TABLE_NAME = 'MyTable1';

SET @query = CONCAT('SELECT * FROM ', @table);
PREPARE stmt FROM @query;
EXECUTE stmt;

edited Apr 22, 2019 at 16:20

answered Apr 22, 2019 at 16:17

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Karwin Over a year ago

You don't need to run the first query with PREPARE/EXECUTE, you can just run the query normally and store its result in the @table variable.

Jeffrey Bane · Accepted Answer · 2019-04-22 16:15:30Z

0

SELECT   *
FROM     (
      SELECT TABLE_NAME
      FROM   information_schema.tables
      WHERE TABLE_NAME = 'MyTable1'
      ) AS Blah

answered Apr 22, 2019 at 16:15

Jeffrey Bane

5922 gold badges10 silver badges42 bronze badges

1 Comment

Bill Karwin Over a year ago

That solves the table alias issue, but it doesn't do what the OP wants, which is to substitute the table name and query from that table.

Jan Macháček · Accepted Answer · 2021-10-25 11:15:15Z

0

Try this:

DECLARE @SELECT nvarchar(500)
SET @SELECT = 'SELECT * FROM ' + @tbl
EXECUTE sp_executesql @SELECT

answered Oct 25, 2021 at 11:15

Jan Macháček

6317 silver badges11 bronze badges

Collectives™ on Stack Overflow

how to get data from dynamic table in sql

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related