sql server语言 - 多个表名之间的sql脚本

Question

 ---------table_01_01_2016----------
|    Name     |       Datetime      |
| Tom         | 01/01/2016 17:50:00 |
| Tomas       | 01/01/2016 17:55:00 |
| Sara        | 01/01/2016 17:58:00 |
-----------------------------------
 ---------table_02_01_2016----------
|    Name     |       Datetime      |
| Tom         | 02/01/2016 16:50:00 |
| Tomas       | 02/01/2016 16:55:00 |
| Sara        | 02/01/2016 16:58:00 |
-----------------------------------
 ---------table_03_01_2016----------
|    Name     |       Datetime      |
| Tom         | 03/01/2016 17:51:00 |
| Tomas       | 03/01/2016 17:52:00 |
| Sara        | 03/01/2016 17:53:00 |
-----------------------------------
.
.
.
.
 ---------table_30_06_2016----------
|    Name     |       Datetime      |
| Tom         | 30/06/2016 17:50:00 |
| Tomas       | 30/06/2016 17:55:00 |
| Sara        | 30/06/2016 17:58:00 |
-----------------------------------

表每天都要创建。我想总结多个表。

 --Summary_01_01_2016 to Summary_30_06_2016--
|           Name      |       Datetime      |
| Tom                 | 01/01/2016 17:50:00 |
| Tomas               | 01/01/2016 17:55:00 |
| Sara                | 01/01/2016 17:58:00 |
| Tom                 | 02/01/2016 16:50:00 |
| Tomas               | 02/01/2016 16:55:00 |
| Sara                | 02/01/2016 16:58:00 | 
| Tom                 | 03/01/2016 17:51:00 |
| Tomas               | 03/01/2016 17:52:00 |
| Sara                | 03/01/2016 17:53:00 |   
.
.
.
.
.
| Tom                 | 30/06/2016 17:50:00 |
| Tomas               | 30/06/2016 17:55:00 |
| Sara                | 30/06/2016 17:58:00 |
---------------------------------------------     

我不脚本连接表在长。谢谢你的帮助^^

Answer 1

您可以使用动态SQL:

DECLARE @sql nvarchar(max)
;WITH cte AS (
SELECT CAST('2016-01-01' as datetime) as d
UNION ALL
SELECT DATEADD(day,1,d)
FROM cte
WHERE d < '2016-06-30'
) -- table contains dates from needed interval
SELECT @sql = (
SELECT 'SELECT * FROM table_' + REPLACE(CONVERT(nvarchar(10),d,104),'.','_') + ' UNION ALL ' + CHAR(10)
FROM cte
FOR XML PATH('')
) --creating a script
OPTION (MAXRECURSION 0)
PRINT @sql
--EXEC sp_executesql @sql --this will execute script

输出:

SELECT * FROM table_01_01_2016 UNION ALL 
SELECT * FROM table_02_01_2016 UNION ALL 
SELECT * FROM table_03_01_2016 UNION ALL 
SELECT * FROM table_04_01_2016 UNION ALL 
SELECT * FROM table_05_01_2016 UNION ALL 
SELECT * FROM table_06_01_2016 UNION ALL 
SELECT * FROM table_07_01_2016 UNION ALL 
SELECT * FROM table_08_01_2016 UNION ALL 
SELECT * FROM table_09_01_2016 UNION ALL 
..

等

Answer 2

很难说，因为你的"问题"缺乏任何有意义的细节。

然而，快到星期五了，我马上就要放假了，所以.......

查看UNION和UNION ALL

也许这对你有帮助。

Answer 3

可以使用all。

Select * From table_01_01_2016
Union All
Select * From table_02_01_2016
Union All
Select...