Truncate Temporal Table Dynamically

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[TruncateTemporalTables]
(@sName VARCHAR(50)
,@tName VARCHAR(50)
)
/*
Purpose: To truncate a temporal table.
This assumes the history table is simply "TableName" + "History"
This enables reseeding the tables, something a simple delete does not do.

EXEC [dbo].[trunc_TemporalTable] 'nbc','Budget'


ALTER TABLE [nbc].[Budget] SET (SYSTEM_VERSIONING = OFF);
TRUNCATE TABLE [nbc].[Budget];
TRUNCATE TABLE [nbc].[BudgetHistory];
ALTER TABLE [nbc].[Budget] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [nbc].[BudgetHistory]) );



*/

AS

BEGIN

SET NOCOUNT ON;

DECLARE @s1 VARCHAR(255)
  , @s2 VARCHAR(255)
  , @s3 VARCHAR(255)
  , @s4 VARCHAR(255);

SET @s1 = 'ALTER TABLE [' + @sName + '].[' + @tName + '] SET (SYSTEM_VERSIONING = OFF);'
SET @s2 = 'TRUNCATE TABLE [' + @sName + '].[' + @tName + '];'
SET @s3 = 'TRUNCATE TABLE [' + @sName + '].[' + @tName + 'History];'
SET @s4 = 'ALTER TABLE [' + @sName + '].[' + @tName + '] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @sName + '].[' + @tName + 'History]) );'

--PRINT @s1
--PRINT @s2
--PRINT @s3
--PRINT @s4

EXECUTE(@s1);
EXECUTE(@s2);
EXECUTE(@s3);
EXECUTE(@s4);

END

GO

Comments

Post a Comment

Popular posts from this blog

Request failed with HTTP status 503 Server Error

Image
Problem: When 100's of users ae trying to access Reporting services report then, SSRS throws error as below : "Request failed with HTTP status 503 Server Error" and how to resolve this error Solution: From experience, this issue can be caused by multiple reasons Problem might be related to: 1 - TCP/IP Port Exhaustion: When a client initiates a TCP/IP socket connection to a server, the client typically connects to a specific port on the server and requests that the server respond to the client over a short-lived TCP or UDP port. The default range of short-lived ports used by client applications is 1025 through 5000. Under certain conditions, it is possible that the available ports in the default range are exhausted. 2 - If you are using any custom assembly method in any of your report (say for example PrivateFontCollection class or anything else), if yes then you might want to review that 3 - Change MaxActiveReqForOneUser from its default value o...
Read more

Removing and Manipulating Time portion from DateTime in SQLServer

Image
One of the common transformation we would have to do as part of ETL would be manipulating Time portion. Please see some of below code, which would help to resolve some of the time related transformation Case1 - Getting only Date portion from DateTime /* Removing Time portion from DateTime */   Select -- Standard date with time   getdate () As DateWithTime , -- Returns Date Portion Only   dateadd ( d , 0 , datediff ( d , 0 , getdate ())) As DateOnly Result as below: Case2 -  /* Removing Seconds from DataTime */ /* Removing Seconds from DataTime */ declare @t datetime set @T = GETDATE () SELECT @T as Original , DATEadd ( ss ,- datepart ( ss , @t ), @t ) as wihtout_sec -- if no milisecs , DATEADD ( mi , DateDiff ( mi , 0 , @t ), 0 ) as OneMore_Variation Case3 - We want to Keep Hours but remove Minutes and Seconds from DateTime /* Keep Hou...
Read more

Calculating Age of the person in T-SQL

One of common calculations, we have to do in ETL is calculating age of the person from the date attribute And this is how I have calculated Age of the person. Select BirthDate , CASE             WHEN Month ( GetDate ()) < Month ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             WHEN Month ( GetDate ()) = Month ( c . [BirthDate] )             AND Day ( GetDate ()) < Day ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             ELSE DateDiff ( yy , c . [BirthDate] , GetDate ())  ...
Read more