0

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.

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
Sheldon Rong
  • 1,506
  • 2
  • 14
  • 19
  • 1
    possible duplicate of [How can I make this query to accept dynamic table names?](http://stackoverflow.com/questions/5966428/how-can-i-make-this-query-to-accept-dynamic-table-names) – Conrad Frix Dec 15 '11 at 18:39

1 Answers1

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
KuldipMCA
  • 3,079
  • 7
  • 28
  • 48
  • 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 – Sheldon Rong Dec 15 '11 at 20:00
  • 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. – KuldipMCA Dec 15 '11 at 20:04