September 26, 2016 at 12:59 pm
Hi,
I've been thinking about this for a while and I can't seem to come up with a viable solution. I have a situation where some tables are named with years (e.g., Sales_2010, Sales_2011, Sales_2012, etc.) I would like to union in a view so that I'm working with a better structure (Id, Year, column1, column2, column3)
SELECT Id, 2010, column1, column2, column3
FROM Sales_2010
UNION ALL
SELECT Id, 2011, column1, column2, column3
FROM Sales_2011
UNION ALL
SELECT Id, 2012, column1, column2, column3
FROM Sales_2012
Problem 1: I can't use dynamic SQL inside of a view, so that complicates things. But OK, let's say that I just build out the next 100 years and UNION ALL future years:
SELECT Id, 2010, column1, column2, column3
FROM Sales_2010
UNION ALL
SELECT Id, 2011, column1, column2, column3
FROM Sales_2011
UNION ALL
SELECT Id, 2012, column1, column2, column3
FROM Sales_2012
UNION ALL
SELECT Id, 2013, column1, column2, column3
FROM Sales_2013
UNION ALL
SELECT Id, 2014, column1, column2, column3
FROM Sales_2014
UNION ALL
SELECT Id, 2015, column1, column2, column3
FROM Sales_2015
UNION ALL
SELECT Id, 2016, column1, column2, column3
FROM Sales_2016
UNION ALL
SELECT Id, 2017, column1, column2, column3
FROM Sales_2017
UNION ALL
SELECT Id, 2018, column1, column2, column3
FROM Sales_2018
.
.
.
Problem 2: SQL Server (rightfully) gives me the error "Invalid object name 'Sales_2018'."
Can anyone think of a way to accomplish this using only a view? Is there a way to trick SQL Server into ignoring the tables that don't yet exist?
Thanks,
Mike
Edit: Added Problem 2
Mike Scalise, PMP
https://www.michaelscalise.com
September 26, 2016 at 1:03 pm
The absolute best solution would be stop naming tables _2008 etc. Add in a transaction year or similar as a column in the table and this is no longer an issue. When you create data structures like this you also create nightmares for getting the data back.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2016 at 1:07 pm
Sean Lange (9/26/2016)
The absolute best solution would be stop naming tables _2008 etc. Add in a transaction year or similar as a column in the table and this is no longer an issue. When you create data structures like this you also create nightmares for getting the data back.
And if users want their 'old tables' back after you have done this, you can give them views ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 26, 2016 at 1:31 pm
This is a vendor's database, so I don't have control over the structure. Well, I mean I do, but I don't want to think about the nightmare that would ensue were I to alter the tables........
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 26, 2016 at 1:47 pm
If you can't change the database design, you need to use dynamic sql to recreate the view constantly.
Here's an idea for a trigger.
IF EXISTS(
SELECT *
FROM sys.triggers
WHERE name = N'RecreateSalesView'
AND parent_class_desc = N'DATABASE'
)
DROP TRIGGER RecreateSalesView ON DATABASE
GO
CREATE TRIGGER RecreateSalesView ON DATABASE
FOR DROP_TABLE, CREATE_TABLE
AS
DECLARE @sql nvarchar(MAX);
IF EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)') LIKE 'Sales[_][0-9][0-9][0-9][0-9]'
AND EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)') = 'TABLE'
BEGIN
SELECT @sql = 'ALTER VIEW Sales AS ' + CHAR(10) +
STUFF((SELECT 'UNION ALL ' + CHAR(10) +
'SELECT Id, ' + RIGHT(name, 4) + ' AS year, column1, column2, column3' + CHAR(10) +
'FROM ' + name + CHAR(10)
FROM sys.tables
WHERE name LIKE 'Sales[_][0-9][0-9][0-9][0-9]'
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 11, '');
IF @sql IS NULL AND OBJECT_ID('Sales', 'V') IS NOT NULL
DROP VIEW Sales;
ELSE
BEGIN
IF OBJECT_ID('Sales', 'V') IS NULL
SET @sql = STUFF( @sql, 1, 5, 'CREATE' )
PRINT @sql
EXEC sp_executesql @sql;
END;
END;
GO
September 26, 2016 at 1:48 pm
Create a job that runs at the start of a year or database trigger on create table that regenerates the view with the tables that do exist. Dynamic SQL to create and run the CREATE VIEW statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2016 at 3:07 pm
Thank you for all of the suggestions. Very helpful.
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply