March 31, 2023 at 12:33 pm
Looking for suggestions\examples of how to update a SP that the table used changes each month at midnight. The app creates a new detail table it logs information to.
example:
sqlt_data_1_2023_03
when 04/01 hits a new table is created called sqlt_data_1_2023_04, and how could I use an automated method to update any SP to the new name, so I don't have to do it manually.
Thanks.
March 31, 2023 at 1:35 pm
What schedules the monthly change? Whatever that process is, just piggy back on it to then automate the procedure update at the same time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2023 at 1:35 pm
(1) You could use dynamic SQL and change the table name literally in the code before executing it.
(2) Or you could instead use a synonym to point to the active table, with static code that references the synonym.
Each method has their own advantages and disadvantages.
(2) For example:
CREATE SYNONYM dbo.sql1_data_active FOR dbo.sqlt_data_1_2023_03;
Then, in the proc, when 04/01 hits, do this:
IF <date_indicates_table_name_needs_changed>
BEGIN
DROP SYNONYM dbo.sql1_data_active;
CREATE SYNONYM dbo.sql1_data_active FOR dbo.sqlt_data_1_2023_04;
END /*IF*/
The main code uses the name "dbo.sql1_data_active" just like the table name would be used.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 31, 2023 at 2:04 pm
I don't have access to the code that creates the new table, or I would piggy back that process..
Just trying to avoid any manual interaction with my SP that uses the new table...
March 31, 2023 at 4:38 pm
Scott's synonym suggestion sounds like your best bet to me.
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
March 31, 2023 at 7:47 pm
Scott's synonym suggestion sounds like your best bet to me.
Strong second here on Scott's synonym suggestion.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2023 at 7:51 pm
Post deleted... I misread what was being done.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2023 at 7:53 pm
Perhaps a better thing to do is to partition the table?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2023 at 8:59 pm
This was removed by the editor as SPAM
March 31, 2023 at 9:26 pm
Okay not sure why my previous post was considered spam so I will try again, perhaps because I tweaked it too much.
There are many ways to handle this but what you do not want to do is constantly modify a stored procedure or have to manually update anything on a regular basis. Here is an alternate solution that can even tie in the Synonym suggestion.
First you create a table that contains the fields TableName, BeginDate, EndDate, and any other elements you feel are needed along with this. Then you can have your Stored Procedure query this table for creating a local temporary table using the dynamic table name. Furter this allows you to populate the TableName ahead of time and/or make quick changes on the fly should the table name not be whhat it was expected to be.
And/Or you could also create a job that runs that calls a stored procedure that runs that Synonym code, so your Stored Procedure never needs to be concerned about the changing table name at all -- a better solution I would think. As you can always rerun the job should you need to update the table name due to it not having been what you expected. This also has the benefit of making your local stored procedures to be more homogenous in their look and feel.
Lastly the Partitioning idea is even better but that requires a bit more work and may not be viable since you would be moving the data out of that table and into your Partition Scheme. Or it requires that you have access to the creation of the table in order to set it up so it can be merged into your Partitioning Scheme and I believe you said you do not have access to this. However, again this would be a much smoother solution over all both from your side as well as the table generators side. So perhaps you bring this up to whomever controls that and maybe work out a better long term solution. Especially if they are eventually archiving these monthly tables. Note partitioning may also be a better solution than whatever created the dynamic creation of these tables to begin with.
March 31, 2023 at 9:50 pm
You don't really need to partition the table if you (1) cluster the table by date first and (2) the lookup queries specify a WHERE clause on a date or date range.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 31, 2023 at 11:46 pm
ScottPletcher that is not entirely true and that is not what I was talking to. I was looking at the bigger picture or the why are they creating these monthly tables with different names and what might they be doing with them short and long term. The ability of a Partition Table to effectively create actual physically different tables while making them appear as one single table has numerous benefits based upon the situation that it may be addressing. To me the constant creation of the same table using different table names to me sounds like ripe pickings for applying the Partitioning solution to. However, not knowing all the details I made the suggestion that it ought to be investigate as a potential smoother long term solution.
But yes there are many different ways to solve the same problem (many wasy to skin a cat) but if you are not aware of them because no one suggests them and you have not subtled across it yet well then that reduces the number of potential solutions and the one that gets eliminated might actually be the best solution. Speak to the problem and the potential problem as the solution may not be the band-aid you just slapped on the problem.
April 1, 2023 at 3:58 am
You don't really need to partition the table if you (1) cluster the table by date first and (2) the lookup queries specify a WHERE clause on a date or date range.
To be sure, I wasn't recommending partitioning for any reasons of performance of queries. Usually, the contrary is true. And, it doesn't have to be TABLE Partitioning. A nice little partitioned view that references only the last couple of tables formed would provide access to the "current" table, much like your excellent suggestion of using a synonym while also allowing for the previous month to be available for reporting purposes. If that's not necessary, then just your synonym suggestion would be the right way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2023 at 2:23 pm
Try this, just as a beginning step, I do not know your requirement. The SP picks the table using a Dynamic SQL statement if you store the newtable name in a database table.
/* Book keeping to support changing/dynamic table name every month
*/DROP TABLE if EXISTS DynamicTable;
SELECT
* INTO DynamicTable
FROM
(
VALUES
(1,('sqlt_data_1_2023_01'), ('2023/01/01'), 'N'),
(2,('sqlt_data_1_2023_02'), ('2023/02/01'), 'N'),
(3,('sqlt_data_1_2023_03'), ('2023/03/01'), 'N'),
(4,('sqlt_data_1_2023_04'), ('2023/04/01'), 'Y')
) t(ID,NewTable, StartDate, Active)
SELECT * FROM DynamicTable
/* Table for March 2023 to support in stored procedure
Sample data
*/DROP TABLE if EXISTS sqlt_data_1_2023_03;
CREATE TABLE sqlt_data_1_2023_03 (col1 VARCHAR(40), col2 VARCHAR(20))
INSERT INTO sqlt_data_1_2023_03 SELECT 'data from March',2023;
/* Table for April 2023 to support in stored procedure
Sample data
*/DROP TABLE if EXISTS sqlt_data_1_2023_04;
CREATE TABLE sqlt_data_1_2023_04 (col1 VARCHAR(40), col2 VARCHAR(20))
INSERT INTO sqlt_data_1_2023_04 SELECT 'data from April ',2023
INSERT INTO sqlt_data_1_2023_04 SELECT 'data from April 1 ', 2023
INSERT INTO sqlt_data_1_2023_04 SELECT 'data from April 2', 2023
GO
/* Stored procedure to support dynamic table
*/CREATE OR ALTER PROCEDURE usp_DynamicTableSP
AS
BEGIN
DECLARE @TableName VARCHAR(40),
@SQL nvarchar(200)
SELECT
@TableName = newTable
FROM
DynamicTable
WHERE
Active = 'Y'
SELECT @TableName AS NewTableName
/* Replace your original table columns here */
SELECT @SQL = 'SELECT COL1, COL2 FROM dbo.' + QUOTENAME(@TableName)
--print @SQL
EXEC sp_executesql @SQL
END;
GO
/* Call Stored procedure
*/EXEC usp_DynamicTableSP;
Note: Make sure you run the UPDATE statement to make the dynamic table row active others inactive using a SQL Agent Job when the new month arrives. You can make March row active and others inactive to check the table is changing dynamically.
=======================================================================
April 1, 2023 at 2:33 pm
(1) You could use dynamic SQL and change the table name literally in the code before executing it.
Sorry, Scott I don't agree with the above. If you check out my solution you are not required to ALTER the SP every month.
=======================================================================
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply