Prepare for Staging Using Dynamic SQL

Prepare for staging and refresh of target table. Staging table name is TARGET table name with _STAGING Appended. TRUNCATE the staging table. Get the MAX DATE LOADED from the TARGET table

Workflow: 

- PREP FOR STAGING

- LOAD STAGING FROM SOURCE

- MERGE TARGET FROM SOURCE

/*

DECLARE @MAX_DATE_LOADED DATETIME;


EXEC [dbo].[PREP_FOR_STAGING]

@P_TABLE_SCHEMA = 'dbo'

, @P_TABLE_NAME = 'TABLE_NAME'

, @P_MAX_DATE_LOADED_OUT = @MAX_DATE_LOADED OUTPUT;


SELECT @MAX_DATE_LOADED;


*/

CREATE PROCEDURE [ETL].[PREP_FOR_STAGING]

@P_TABLE_SCHEMA NVARCHAR(50)

, @P_TABLE_NAME NVARCHAR(50)

, @P_MAX_DATE_LOADED_OUT DATETIME OUTPUT

AS

BEGIN


DECLARE

@MAX_DATE_LOADED DATETIME = CONVERT(DATE, GETDATE()) 

, @SQL NVARCHAR(4000);


-- truncate the staging table

SET @SQL = CONCAT(

N'TRUNCATE TABLE '

    , @P_TABLE_SCHEMA

    , N'.'

    , @P_TABLE_NAME

, N'_STAGING'

);


EXEC sp_executesql @SQL;

-- get the MAX([DATE_LOADED]) from the target table

SET @SQL = CONCAT(

    N'SELECT @MAX_DATE_LOADED_OUT = MAX([DATE_LOADED]) '

    , N'FROM '

    , @P_TABLE_SCHEMA

    , N'.'

    , @P_TABLE_NAME

    );


EXEC sp_executesql

@SQL

, N'@MAX_DATE_LOADED_OUT DATETIME OUTPUT'

, @MAX_DATE_LOADED_OUT = @MAX_DATE_LOADED OUTPUT;


-- Handle edge case where @LAST_MODIFIED IS NULL;

-- this will happen if the table is empty or [DATE_LOADED] is

-- actually NULL in every row

SET @P_MAX_DATE_LOADED_OUT = 

IIF(@MAX_DATE_LOADED IS NOT NULL

, @MAX_DATE_LOADED

, CONVERT(DATETIME, CONVERT(DATE, DATEADD(year, -5, GETDATE())))

);

END

GO



Comments

Post a Comment

Popular posts from this blog

Using sp_executesql with OPENQUERY

 gemini prompt: give me the in depth details on using openquery in sql server with the sp_executesql stored procedure The OPENQUERY function in SQL Server allows you to execute a pass-through query on a specified linked server . By design, OPENQUERY 's arguments—the linked server name and the query string— do not accept local T-SQL variables . However, you can overcome this limitation, particularly for parameterizing the query string, by using dynamic SQL executed via the system stored procedure sp_executesql . Here is an in-depth breakdown of how to use OPENQUERY with sp_executesql to include variable values in your remote query. 1. The Challenge: Parameterizing OPENQUERY The fundamental restriction of OPENQUERY is its syntax: SQL OPENQUERY ( linked_server , 'query' ) Neither linked_server nor the 'query' string can be a local SQL Server variable (e.g., @MyLinkedServerName or @MyQuery ). To pass a variable (like a specific CustomerID or a date range) into...
Read more

Executing Remote Queries Safely and Efficiently with sp_executesql

 gemini prompt: provide a blog post by a database professional on the best practice for using sp_executesql to query a remote sql server database Executing Remote Queries Safely and Efficiently with sp_executesql As database professionals, we often face the need to query data from a remote SQL Server instance . While options like simple four-part names (e.g., [Server].[Database].[Schema].[Table] ) or OPENQUERY exist, using sp_executesql in conjunction with linked servers provides a powerful, secure, and flexible approach, especially when dealing with dynamic SQL. Here's a breakdown of the best practices for leveraging sp_executesql for remote queries. 1. Why sp_executesql for Remote Queries? When executed locally, sp_executesql is primarily used to execute dynamic SQL safely by allowing you to parameterize the query string, which is the fundamental defense against SQL injection . When querying a linked server, you can use it to execute code directly on the remote server...
Read more