SQL Server : How to fetch data from dynamic multiple tables?

Question

Using SQL SERVER.

The database contains data from different years, somehow I want to fetch all the data (all the years) and show to the user, for example, database contains table:

table
--------
records_2000_01
records_2000_02
records_2000_03
...

now through select TABLE_NAME into @tableName from information_schema.tables where table_name like 'records_%'

I can fetch all the table name, how to write a SQL (or perhaps procedure) to fetch all data from these tables? make all record into one table?

thx.

possible duplicate of How can I make this query to accept dynamic table names? — Conrad Frix
– Conrad Frix, Commented Dec 15, 2011 at 18:39

KuldipMCA · Accepted Answer · 2011-12-15 19:56:41Z

1

Create procedure in this Below steps you need to follow.

Create table Common_Table with same one structure which all your table like records_2000_02 and others.
Use loop or cursor for all records you get from information_schema which match your table name
Create dynamic sql like ' Insert into your Common_Table select * from ' + Table_name that you get from Information_schema.
- Then you get all your data in one table Common_Table

answered Dec 15, 2011 at 19:56

KuldipMCA

3,1617 gold badges29 silver badges48 bronze badges

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

2 Comments

Sheldon Rong Over a year ago

yep, i already get it working, but I can;t post the answer yet, since my "reputation is too low", I cannot answer my own question.... what the heck is this :P

KuldipMCA Over a year ago

You can need to visit site regular and contribution to the community so you get more reputation and you can do that thing in future.

Collectives™ on Stack Overflow

SQL Server : How to fetch data from dynamic multiple tables?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related