June 12, 2012 at 8:10 am
Hey all,
Trying to figure out the best course of action for taking a given data set that has a start date and inserting a record for all values for every possible date between the start date and a specified end date.
So for example, I have a servername, database name, first backup date in a cursor. So say I have Server "X", database "Y" and date "2012.06.01". I now need to insert 12 records into my table with Server X, Database Y and all possible dates from 2012.06.01 to today.
Currently I'm looking at a double cursor (not a fan); however I'm open to suggestions. If this is even the route, I'm still not entirely clear on how to even proceed at this point. I can not join a calendar table / CTE to my data set as there is nothing to join on ...
If this made sense, any help is most appreciated.
Thanks
June 12, 2012 at 8:14 am
Adam Bean (6/12/2012)
Hey all,Trying to figure out the best course of action for taking a given data set that has a start date and inserting a record for all values for every possible date between the start date and a specified end date.
So for example, I have a servername, database name, first backup date in a cursor. So say I have Server "X", database "Y" and date "2012.06.01". I now need to insert 12 records into my table with Server X, Database Y and all possible dates from 2012.06.01 to today.
Currently I'm looking at a double cursor (not a fan); however I'm open to suggestions. If this is even the route, I'm still not entirely clear on how to even proceed at this point. I can not join a calendar table / CTE to my data set as there is nothing to join on ...
If this made sense, any help is most appreciated.
Thanks
Don't use a cursor. Use a tally table. Start by reading this.[/url]
Jared
CE - Microsoft
June 12, 2012 at 8:18 am
Thanks, I do have a calendar table; however the problem remains, inserting a new record based on a source data set with every possible date between a specified range.
June 12, 2012 at 8:21 am
Adam Bean (6/12/2012)
Thanks, I do have a calendar table; however the problem remains, inserting a new record based on a source data set with every possible date between a specified range.
Did you read the article on how to use a tally table? I'm pretty sure that you can apply a tally table (not a calendar table) to do this easily.
Jared
CE - Microsoft
June 12, 2012 at 8:22 am
You don't need the cursor! If you have the calendar table, just select records you need filtering by date.
Or you can use some run-time tally table like that:
DECLARE @DateFrom DATETIME
SET @DateFrom = '20120601'
SELECT TOP (DATEDIFF(DAY,@DateFrom,GETDATE()) + 1) DATEADD(DAY,ROW_NUMBER()
OVER (ORDER BY [object_id]) -1, @DateFrom ) AS MyDate
, @@SERVERNAME AS ServerName
, DB_NAME() AS DatabaseName
FROM sys.columns
You can see the maximum number of records it can return is limited by number of records in sys.columns, if you need more you can create Cartesian product of this table...
If you need some help with use of your calendar table, please post its DDL.
June 12, 2012 at 8:32 am
Here is a quick sample.
CREATE TABLE #testtally (n int)
INSERT INTO #testtally
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @dateDiff int
SET @startDate = '2012-06-12'
SET @endDate = '2012-06-14'
SET @dateDiff = DATEDIFF(d,@startdate, @enddate)
SELECT DATEADD(d, t.n, @startdate), 'databaseX', 'somethingelse'
FROM #testtally t
WHERE n <= @datediff
Jared
CE - Microsoft
June 12, 2012 at 8:55 am
Eugene Elutin (6/12/2012)
You don't need the cursor! If you have the calendar table, just select records you need filtering by date.Or you can use some run-time tally table like that:
DECLARE @DateFrom DATETIME
SET @DateFrom = '20120601'
SELECT TOP (DATEDIFF(DAY,@DateFrom,GETDATE()) + 1) DATEADD(DAY,ROW_NUMBER()
OVER (ORDER BY [object_id]) -1, @DateFrom ) AS MyDate
, @@SERVERNAME AS ServerName
, DB_NAME() AS DatabaseName
FROM sys.columns
You can see the maximum number of records it can return is limited by number of records in sys.columns, if you need more you can create Cartesian product of this table...
If you need some help with use of your calendar table, please post its DDL.
You sir, are my hero ... this is perfect and accomplished exactly what I wanted to do!
Thank you!
June 12, 2012 at 9:10 am
Adam Bean (6/12/2012)
Eugene Elutin (6/12/2012)
You don't need the cursor! If you have the calendar table, just select records you need filtering by date.Or you can use some run-time tally table like that:
DECLARE @DateFrom DATETIME
SET @DateFrom = '20120601'
SELECT TOP (DATEDIFF(DAY,@DateFrom,GETDATE()) + 1) DATEADD(DAY,ROW_NUMBER()
OVER (ORDER BY [object_id]) -1, @DateFrom ) AS MyDate
, @@SERVERNAME AS ServerName
, DB_NAME() AS DatabaseName
FROM sys.columns
You can see the maximum number of records it can return is limited by number of records in sys.columns, if you need more you can create Cartesian product of this table...
If you need some help with use of your calendar table, please post its DDL.
You sir, are my hero ... this is perfect and accomplished exactly what I wanted to do!
Thank you!
I'm confused... Here you have no "specified end date."
EDIT: Of course it can be added easily... Great solution Eugene! I can't believe how fast those window functions can be.
Jared
CE - Microsoft
June 12, 2012 at 9:49 am
SQLKnowItAll (6/12/2012)
Adam Bean (6/12/2012)
...
I'm confused... Here you have no "specified end date."
I guess, it's a kind of achievement to confuse KnowItAll one 🙂
In reality for the given requirement, You don't need to know "end date", just number of days since the requested "start" is enough...
.. I can't believe how fast those window functions can be.
Especially when you can limit number of rows affected...
June 12, 2012 at 9:57 am
Eugene Elutin (6/12/2012)
I guess, it's a kind of achievement to confuse KnowItAll one 🙂
I wish that was true... I'm always confused, though less so since I have been on the SSC forums 🙂
Jared
CE - Microsoft
June 13, 2012 at 1:08 pm
Eugene, I'm hoping you can save me yet again.
Have another similar problem that I'm struggling with.
This is my current query:
INSERT INTO #Backups
([CalendarDate], [ServerName], [DBName], [RecoveryModel], [IsTlogShipped])
SELECT TOP
(DATEDIFF(DAY,CONVERT(VARCHAR,@FirstBackupDate,102),GETDATE()) + 1) DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY [CalendarDate]) -1, @FirstBackupDate) AS [CalendarDate]
,@ServerName
,@DBName
,@RecoveryModel
,@IsTlogShipped
FROM [dbo].[Calendar]
Now, I need to add logic that actually inserts 1 or multiple records based the state of @RecoveryModel. So for example ... if @RecoveryModel != 'SIMPLE' then I will insert one record with a value of D and one with a value of L. If @RecoveryModel = 'SIMPLE', then it would just be one for D.
I hope that made sense ... this query is crazy out of control and I know what I'm trying to do, but trying to make it as simple as possible without utilizing multiple temp tables and continually modifying the data.
Thanks
June 13, 2012 at 2:05 pm
INSERT INTO #Backups
([CalendarDate], [ServerName], [DBName], [RecoveryModel], [IsTlogShipped])
SELECT TOP
(DATEDIFF(DAY,CONVERT(VARCHAR,@FirstBackupDate,102),GETDATE()) + 1) DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY [CalendarDate]) -1, @FirstBackupDate) AS [CalendarDate]
,@ServerName
,@DBName
,'D'
,@IsTlogShipped
FROM [dbo].[Calendar]
WHERE @RecoveryModel IN ('D','L')
UNION ALL
SELECT TOP
(DATEDIFF(DAY,CONVERT(VARCHAR,@FirstBackupDate,102),GETDATE()) + 1) DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY [CalendarDate]) -1, @FirstBackupDate) AS [CalendarDate]
,@ServerName
,@DBName
,@RecoveryModel
,@IsTlogShipped
FROM [dbo].[Calendar]
WHERE @RecoveryModel = 'L'
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 13, 2012 at 2:13 pm
Hi Wayne and thanks.
I like the idea; however that won't get me what I need. In by that, if the recovery model is not simple, I need to insert two type records ... one for D, one for L; whereas if it is simple, I need to just insert one record of type D. In your query, I'll only still get one insert if the recovery != 'SIMPLE' ... make sense?
I don't know how to use it properly; however I would assume a CASE within the OVER would do the job ...
June 13, 2012 at 2:17 pm
What column is the 'D' or 'L' getting inserted to?
Jared
CE - Microsoft
June 13, 2012 at 2:19 pm
Like this?
INSERT INTO #Backups
([CalendarDate], [ServerName], [DBName], [RecoveryModel], [IsTlogShipped])
SELECT TOP
(DATEDIFF(DAY,CONVERT(VARCHAR,@FirstBackupDate,102),GETDATE()) + 1) DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY [CalendarDate]) -1, @FirstBackupDate) AS [CalendarDate]
,@ServerName
,@DBName
,'D'
,@IsTlogShipped
FROM [dbo].[Calendar]
UNION ALL
INSERT INTO #Backups
([CalendarDate], [ServerName], [DBName], [RecoveryModel], [IsTlogShipped])
SELECT TOP
(DATEDIFF(DAY,CONVERT(VARCHAR,@FirstBackupDate,102),GETDATE()) + 1) DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY [CalendarDate]) -1, @FirstBackupDate) AS [CalendarDate]
,@ServerName
,@DBName
,'L'
,@IsTlogShipped
FROM [dbo].[Calendar]
WHERE @RecoveryModel <> 'SIMPLE'
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply