June 8, 2012 at 5:29 pm
:unsure:
So i have the problem at work where i am rolling (union all) a bunch of tables together and to make it a little easier i want to be able to add future tables to the queries so that i dont have to worry about them for a few months.
How would i do this?
Is there a way that it will check if there is a table and if there is roll it up and if there isnt skip and go to the next line?
June 8, 2012 at 5:43 pm
Check the IF EXISTS statement using Books On Line or GOOGLE, this may be what you are loooking for.
June 8, 2012 at 5:47 pm
ive looked at that...it seems like i would have to write the code to union things each time if i am not mistaken.
And i have 200 tables that are being combined. 😕
June 8, 2012 at 5:56 pm
May I suggest that you post the T-SQL statement that you think you might be using and then some one will be able to assist you further.
To post a few of the table definitions, and your code, click on the first link in my signature block, the article also contains some T-SQL that will allow you to rapidly and easily supply sufficient information so that some one will be able to assist you further
June 8, 2012 at 6:21 pm
slunt01 (6/8/2012)
:unsure:So i have the problem at work where i am rolling (union all) a bunch of tables together and to make it a little easier i want to be able to add future tables to the queries so that i dont have to worry about them for a few months.
How would i do this?
Is there a way that it will check if there is a table and if there is roll it up and if there isnt skip and go to the next line?
No, not easily.
You'll be looking to create some dynamic SQL in tandem with sp_MSforeachtable. Not what I'd usually recommend but it's really the only way out of it.
If you have options on the original design where you're including tables like this that have an expected iteration and why you're doing so, we can probably help you clean up your general design to avoid the problem completely.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 9, 2012 at 8:42 am
slunt01 (6/8/2012)
:unsure:So i have the problem at work where i am rolling (union all) a bunch of tables together and to make it a little easier i want to be able to add future tables to the queries so that i dont have to worry about them for a few months.
How would i do this?
Is there a way that it will check if there is a table and if there is roll it up and if there isnt skip and go to the next line?
I've done this many times and there is a way to make it so you never have to worry about adding extra tables because you can schedule a "trick" to run once a month. And, no... we won't need to use sp_MSforeachtable.
The key, however, will be the format of the table names themselves. They must be named in a consistent, temporal fashion in order to automate it all. With that thought in mind what is the format of the table NAMES?
--Jeff Moden
June 11, 2012 at 3:29 am
Completely agree with Jeff, the best way is to standardize the table names and write t-sql code and schedule as a weekly or monthly job.
=======================================================================================
Visit my technical reference; you might find some of your issues already documented.
June 12, 2012 at 3:06 pm
the table names are like AB_201201 then AB_201202 and so on.
there are a few variations of the AB but other than those few the only think that changes is the month on the year month indicator
June 12, 2012 at 9:58 pm
I believe this will help with the original question.
You don't want to change your code every couple of months. Point the code to a view instead of a bunch of table names. In this case, this is a "partitioned view" which works in both the Standard and Enterprise editions of SQL Server. If you don't need different "sizes" of views (previous 3 months, previous 6 months, previous quarter, previous year, etc, etc), you could use a partitioned table, instead.
Please take a look in Books Online for "Partitioned Views" for additional information. If you do things right, they can even be updatable (which is what Itried to do in the example code).
First (and [font="Arial Black"]please do this in TEMPDB so we don't mess any real stuff up[/font]), let's say you have 7 months worth of data stored in separate tables already, including the current month. They look like this...
CREATE TABLE dbo.AB_201112
(
SomeCol1 INT NOT NULL,
SomeCol2 VARCHAR(25),
SomeDate DATETIME NOT NULL
CHECK (SomeDate >= '20111201' AND SomeDate < '20120101'),
CONSTRAINT PK_201112 PRIMARY KEY CLUSTERED (SomeDate,SomeCol1)
);
;
CREATE TABLE dbo.AB_201201
(
SomeCol1 INT NOT NULL,
SomeCol2 VARCHAR(25),
SomeDate DATETIME NOT NULL
CHECK (SomeDate >= '20120101' AND SomeDate < '20120201'),
CONSTRAINT PK_201201 PRIMARY KEY CLUSTERED (SomeDate,SomeCol1)
);
;
CREATE TABLE dbo.AB_201202
(
SomeCol1 INT NOT NULL,
SomeCol2 VARCHAR(25),
SomeDate DATETIME NOT NULL
CHECK (SomeDate >= '20120201' AND SomeDate < '20120301'),
CONSTRAINT PK_201202 PRIMARY KEY CLUSTERED (SomeDate,SomeCol1)
);
;
CREATE TABLE dbo.AB_201203
(
SomeCol1 INT NOT NULL,
SomeCol2 VARCHAR(25),
SomeDate DATETIME NOT NULL
CHECK (SomeDate >= '20120301' AND SomeDate < '20120401'),
CONSTRAINT PK_201203 PRIMARY KEY CLUSTERED (SomeDate,SomeCol1)
);
;
CREATE TABLE dbo.AB_201204
(
SomeCol1 INT NOT NULL,
SomeCol2 VARCHAR(25),
SomeDate DATETIME NOT NULL
CHECK (SomeDate >= '20120401' AND SomeDate < '20120501'),
CONSTRAINT PK_201204 PRIMARY KEY CLUSTERED (SomeDate,SomeCol1)
);
;
CREATE TABLE dbo.AB_201205
(
SomeCol1 INT NOT NULL,
SomeCol2 VARCHAR(25),
SomeDate DATETIME NOT NULL
CHECK (SomeDate >= '20120501' AND SomeDate < '20120601'),
CONSTRAINT PK_201205 PRIMARY KEY CLUSTERED (SomeDate,SomeCol1)
);
;
CREATE TABLE dbo.AB_201206
(
SomeCol1 INT NOT NULL,
SomeCol2 VARCHAR(25),
SomeDate DATETIME NOT NULL
CHECK (SomeDate >= '20120601' AND SomeDate < '20120701'),
CONSTRAINT PK_201206 PRIMARY KEY CLUSTERED (SomeDate,SomeCol1)
);
;
You could run code at the first instant of each month that would create a table for next month and automatically recreate the partitioned view. Like this...
--===== Declare and preset some obviously named variables
DECLARE @SQLCreateTable NVARCHAR(MAX),
@SQLCreateView NVARCHAR(MAX),
@StartDate NCHAR(8),
@EndDate NCHAR(8),
@YYYYMM SYSNAME
;
SELECT @StartDate = CONVERT(CHAR(8),DATEADD(mm,DATEDIFF(mm, 0,GETDATE()),0),112),
@EndDate = CONVERT(CHAR(8),DATEADD(mm,DATEDIFF(mm,-1,GETDATE()),0),112),
@YYYYMM = CONVERT(CHAR(6),DATEADD(mm,1,GETDATE()),112)
;
SELECT @SQLCreateTable = N'
CREATE TABLE dbo.AB_@YYYYMM
(
SomeCol1 INT NOT NULL,
SomeCol2 VARCHAR(25),
SomeDate DATETIME NOT NULL
CHECK (SomeDate >= ''@StartDate'' AND SomeDate < ''@EndDate''),
CONSTRAINT PK_@YYYYMM PRIMARY KEY CLUSTERED (SomeDate,SomeCol1)
);'
;
--=======================================================================================
-- This section creates a table for next month if it does not already exist.
--=======================================================================================
--===== If the table doesn't already exist...
IF OBJECT_ID(N'dbo.AB_' + @YYYYMM) IS NULL
BEGIN
--===== Create the dynamic SQL to create a dated table name and execute it
SELECT @SQLCreateTable =
REPLACE(
REPLACE(
REPLACE(
@SQLCreateTable
,N'@StartDate',@StartDate)
,N'@EndDate',@EndDate)
,N'@YYYYMM',@YYYYMM)
;
RAISERROR ('Creating table AB_%s',0,1,@YYYYMM) WITH NOWAIT;
EXEC (@SQLCreateTable);
END
ELSE RAISERROR ('Table AB_%s already exists. Continuing...',0,1,@YYYYMM) WITH NOWAIT;
;
--=======================================================================================
-- This section builds a view for the previous 6 months and the current month.
-- You could do the same with virtually any time span you have tables for.
--=======================================================================================
--===== Add a conditional drop of the view to the Dynamic SQL
SELECT @SQLCreateView = '
IF OBJECT_ID(''dbo.AB_Previous6Months'') IS NOT NULL DROP VIEW dbo.AB_Previous6Months;
'
;
--===== We have to drop the view separately from the creation so do the drop now.
EXEC (@SQLCreateView);
--===== Start the CREATE VIEW code
SELECT @SQLCreateView = ' CREATE VIEW dbo.AB_Previous6Months AS
';
--===== Add the code to rebuild the view with the correct table names
SELECT @SQLCreateView = @SQLCreateView
+ STUFF(
(
SELECT ' UNION ALL ' + CHAR(10)
+ 'SELECT SomeCol1, SomeCol2 FROM AB_'
+ CONVERT(CHAR(6),DATEADD(mm,-Number,GETDATE()),112)
FROM master.dbo.spt_values
WHERE Type = 'P'
AND Number BETWEEN 0 AND 6
FOR XML PATH('')
)
,1,11,'')
;
--===== Build the view
RAISERROR ('Creating the AB_Previous6Months view.',0,1) WITH NOWAIT;
EXEC (@SQLCreateView);
print @sqlcreateview
Set it up as a scheduled job to run right at midnight on the first of the month and you'll just about never have to worry about it ever again. The only time that you'll even need to think about it is if someone changes the design of the table. Of course, you'd have to worry about that as it is any way.
I didn't use "Select *" in any of this because it's against my nature and if someone changes any of the underlying tables without your knowledge, then "SELECT *" could actually return some incorrect answers.
As a bit of a side bar, there are some HUGE ease-of-maintenance advantages to using either a Partitioned View or a Partitioned Table. For example, if you have a table that contains only 1 month of data from a year ago, there's a pretty good chance that no one has updated anything in it for the previous month... or at least not much. That means that the indexes haven't fragged much, if at all, and you don't need to reindex that part of the "table" or "view". Chances are, only the latest month's or two tables will actually need any index work done and, since they much smaller than if all the data was in a single table, index maintenance is going to take a fair bit less time.
--Jeff Moden
June 14, 2012 at 4:26 pm
WOW ok i will look at this some more when i have some tinker time.
THANK YOU for your help. I am really hoping this works. it would make it SUPER easy and user friendly. 🙂
June 19, 2012 at 5:47 pm
Thankgs for the feedback. Heh... I'm a "lazy" DBA. I like things that maintain themselves. When you get the chance, let me know how this all works out for you.
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply