Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

How to Full Load a Table with a Script


Overview

Beginning with Jet Enterprise 2016 setting a Full Load on tables that are incrementally loaded is supported natively in the Jet Data Manager. This is covered in the KB article here: Creating an Execution Package . However there may be times where more control is needed over when the full load is done. The process below outlines the steps to set up a script that will trigger a full load on all incrementally loaded tables in the data warehouse and staging database.

Process

The script below will automatically truncate all _I and _V tables in the data warehouse and staging database which will, in turn, trigger a full load to be processed on all of these tables during the execution process. This script should be added in an appropriate place as a script action on the staging database and the database name highlighted in yellow below should be replaced with the name of the data warehouse SQL database.


DECLARE

@SqlCommandStage nvarchar(2000)

,@SqlCommandDwh nvarchar(2000)


-- Stage

SET @SqlCommandStage =

'DECLARE @SqlString nvarchar(2000)

IF ''?'' LIKE ''%_INCR]''

BEGIN

SET @SqlString = ''TRUNCATE TABLE ?; TRUNCATE TABLE '' + SUBSTRING(''?'', 1, LEN(''?'') - 5) + ''V];''

EXEC sp_executesql @SqlString

END'


-- Data Warehouse

SET @SqlCommandDwh =

'DECLARE @SqlString nvarchar(2000)

IF ''?'' LIKE ''%_I]''

BEGIN

SET @SqlString = ''TRUNCATE TABLE ?; TRUNCATE TABLE '' + SUBSTRING(''?'', 1, LEN(''?'') - 2) + ''V];''

EXEC sp_executesql @SqlString

END'


IF DATEPART(dw, GETDATE()) = 7

BEGIN

EXEC [sys].[sp_MSforeachtable] @command1 = @SqlCommandStage

EXEC [ JetNavDwh ].[sys].[sp_MSforeachtable] @command1 = @SqlCommandDwh

END

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments