SQL Server:如何从动态多个表中获取数据? [英] SQL Server : How to fetch data from dynamic multiple tables?

问题描述

使用SQL SERVER.

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
...

现在通过 从information_schema.tables中选择TABLE_NAME到@tableName中，其中table_name如'records _％'

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

我可以获取所有表名，如何编写SQL(或过程)以从这些表中获取所有数据?将所有记录都放在一个表中?

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.

推荐答案

在此创建过程您需要遵循以下步骤.

Create procedure in this Below steps you need to follow.

• Create table Common_Table具有与您的所有表(如records_2000_02和其他表)相同的一种结构.
• 将循环或光标用于从information_schema中获得的，与表名匹配的所有记录
• 创建从Information_schema获得的动态SQL(如' Insert into your Common_Table select * from ' + Table_name).
  • 然后将所有数据存储在一个表Common_Table中
  • 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

    这篇关于SQL Server:如何从动态多个表中获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！