November 20, 2017 at 1:55 am
Hi guys,
I have loads of stored procs that gather data but need an option to be inserted into different tables.
Basically they will gather the same data but at different intervals that don't mix like monthly and weekly. The problem is that the monthly version is already complete and been used for a year now someone thought that we should add a weekly version to it, but if I add the weekly data to the monthly one it would get confused unless I add an interval column to it, but if I do that I would have to fix everything that's connected to the data table.
I was trying stuff like INSERT INTO @TableName
SELECT...FROM.....
but that just puts it into the parameter
Now I'm thinking about running it from a string likeDECLARE @sqlstr NVARCHAR(MAX) = 'INSERT INTO ''' + @TableName + '''
SELECT...FROM.....'
sp_executesql @sqlstr
Is there any easier more standard way to do this please?
Thanks
November 20, 2017 at 2:19 am
I think I would have separate stored procedures - one to insert into the monthly table and one to insert into the weekly.
John
November 20, 2017 at 2:56 am
davidvarga086 - Monday, November 20, 2017 1:55 AMHi guys,I have loads of stored procs that gather data but need an option to be inserted into different tables.
Basically they will gather the same data but at different intervals that don't mix like monthly and weekly. The problem is that the monthly version is already complete and been used for a year now someone thought that we should add a weekly version to it, but if I add the weekly data to the monthly one it would get confused unless I add an interval column to it, but if I do that I would have to fix everything that's connected to the data table.
I was trying stuff likeINSERT INTO @TableName
SELECT...FROM.....
but that just puts it into the parameterNow I'm thinking about running it from a string like
DECLARE @sqlstr NVARCHAR(MAX) = 'INSERT INTO ''' + @TableName + '''
SELECT...FROM.....'
sp_executesql @sqlstrIs there any easier more standard way to do this please?
Thanks
Question, is this the same data for both months and weeks? If that is the case, why not just aggregate on either as needed? That would only require a single date column and the different grouping for the aggregation is then applied to that column.
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @TDATE DATETIME = GETDATE();
SELECT
YEAR(@TDATE) AS TD_YEAR
,MONTH(@TDATE) AS TD_MONTH
,DATEPART(WEEK,@TDATE) AS TD_WEEK
;
Note that a "week" can mean different things, better be clear on which definition to apply 😉
November 20, 2017 at 3:22 am
John Mitchell-245523 - Monday, November 20, 2017 2:19 AMI think I would have separate stored procedures - one to insert into the monthly table and one to insert into the weekly.John
I thought about that but it would be a bad choice for maintenance. If one of the calculations change in a stored proc I would have to fix it in three places instead of 1 (monthly, weekly, daily) and I would have to create 350+ new stored procedures for weekly plus 350+ for daily. The daily calculations are just talk for now, but I bet I will have to do them after the weekly ones are finished.
Eirikur Eiriksson - Monday, November 20, 2017 2:56 AMdavidvarga086 - Monday, November 20, 2017 1:55 AMHi guys,I have loads of stored procs that gather data but need an option to be inserted into different tables.
Basically they will gather the same data but at different intervals that don't mix like monthly and weekly. The problem is that the monthly version is already complete and been used for a year now someone thought that we should add a weekly version to it, but if I add the weekly data to the monthly one it would get confused unless I add an interval column to it, but if I do that I would have to fix everything that's connected to the data table.
I was trying stuff likeINSERT INTO @TableName
SELECT...FROM.....
but that just puts it into the parameterNow I'm thinking about running it from a string like
DECLARE @sqlstr NVARCHAR(MAX) = 'INSERT INTO ''' + @TableName + '''
SELECT...FROM.....'
sp_executesql @sqlstrIs there any easier more standard way to do this please?
Thanks
Question, is this the same data for both months and weeks? If that is the case, why not just aggregate on either as needed? That would only require a single date column and the different grouping for the aggregation is then applied to that column.
😎
USE TEEST;
GO
SET NOCOUNT ON;DECLARE @TDATE DATETIME = GETDATE();
SELECT
YEAR(@TDATE) AS TD_YEAR
,MONTH(@TDATE) AS TD_MONTH
,DATEPART(WEEK,@TDATE) AS TD_WEEK
;Note that a "week" can mean different things, better be clear on which definition to apply 😉
I can't just do a daily cause some of the calculation have to be monthly, weekly or daily. if I aggregate up the daily ones to weekly or monthly the results would be inaccurate.
November 20, 2017 at 3:30 am
davidvarga086 - Monday, November 20, 2017 3:22 AMIf one of the calculations change in a stored proc I would have to fix it in three places instead of 1 (monthly, weekly, daily)
Not if you modularise the calculations with stored procedures or functions.
I would have to create 350+ new stored procedures for weekly plus 350+ for daily.
I don't understand. Why 350?
John
November 20, 2017 at 4:07 am
John Mitchell-245523 - Monday, November 20, 2017 3:30 AMdavidvarga086 - Monday, November 20, 2017 3:22 AMIf one of the calculations change in a stored proc I would have to fix it in three places instead of 1 (monthly, weekly, daily)Not if you modularise the calculations with stored procedures or functions.
I would have to create 350+ new stored procedures for weekly plus 350+ for daily.
I don't understand. Why 350?John
modularise? I'll give you an example. One of our calculations gets the average number of tickets closed on a monthly bases. If I do it on a daily bases then average it again for weekly or monthly that would be inaccurate.
If I would wan't to fix it to a format that would be good for all three I would have to fix a lot of stuff not just the stored procedures end user stuff as well like (reports on pages, we have an app that creates a word document with charts and tables gathered from this and mail subscriptions of some of the calculations) and go through a lot of red tape.
356 is the amount of calculation types that we currently have but the number will go higher in the future. all the calculations are in separate stored procedures, the reason for that is that each one of them can connect to different linked servers and we don't always need to run all of them sometimes running 30 is enough(so we lessen the load on the linked servers), but sometimes all of them have to go. and it's easier for maintenance.
November 20, 2017 at 10:42 am
Not necessarily. Views might be able to solve this for you.
1) Rename the existing table.
2) Add a column to the existing table for interval, defaulting it to monthly (so that the monthly code does not have to be changed).
3) Create a view with the same name and columns as the original table, including a WHERE interval = 'monthly' in the view.
4) The code that loads the new weekly values will INSERT to the new table name, while the old monthly code points to the view name (but naturally both will load to and read from the same physical table).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 21, 2017 at 5:34 am
First solve the problem then write the code !
December 7, 2017 at 11:15 am
You could change the sp to write to a temp table and then based on a parameter value copy the temp table data to either of your permanent table versions:
select xx, yy, zz
into #myTempCopy
from youExistingTableLogic;
If @OutputVersion = 1
insert into dbo.MonthlyData
select *
from #myTempCopy
else if @OutputVersion = 2
insert into dbo.WeeklyData
select *
from #myTempCopy
else if @OutputVersion = 3
insert into dbo.QuarterlyData
select *
from #myTempCopy
drop table #myTempCopy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply