Creating a dynamic view with multiple tables

user_2675072 0
Viewed 674

We have a database that creates a new table for transactions every month. So there are tables like TRANS201811, TRANS201812, TRANS201901, etc. I need a view that shows all of these transactions in the view. Below is the dynamic SQL I've written that accomplishes what I am trying to do. However, I'm trying to see if there is a more efficient way of writing this. Thanks!

declare @sql as varchar(max) = (
            SELECT ltrim(stuff((
            SELECT ' UNION SELECT ACCOUNT, AMOUNT, GL_FROM, GL_TO, INIT_ACCNT, LOGGED, TRAN_WHEN FROM ' + cast(t.table_schema + '.' + t.table_name AS VARCHAR(max)) + ' '
            FROM INFORMATION_SCHEMA.TABLES t
            WHERE TABLE_SCHEMA = 'softwareadmin'
                            AND (TABLE_NAME LIKE 'TRANS20%')
            FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 6, N'')))

exec(@sql)
tsql sql-server sql
1 Answers

You could create a simple view that shows all of them:

CREATE VIEW AS transactionViews
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND [TABLE_NAME] LIKE 'TRANS20%'
user_81855130
Related