CODEWITHSUNDEEP
sqlsql-serversql-server-2008procedure
Sheldon Rong
Sheldon Rong

Reputation: 1506

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

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.

Upvotes: 0

Views: 2301

Answers (1)

KuldipMCA
KuldipMCA

Reputation: 3149

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

Upvotes: 1

Related Questions