Follow

How to do a periodical full Load on Incremental

If you want to do a periodical full load of all tables in a database, the script below will trigger it when executed. 

You add the script as a Script Action. Right click on a business unit, go to Advanced and select Set Pre and Post Scripts. Then you can select the script in the pre section (first dropdown). This will execute the script before the business unit is executed.

IF DATEPART(dw,GETDATE())= 7 -- 7 is Saturday, 1 is Sunday
 BEGIN
 DECLARE @sql NVARCHAR(2000)
 
 SET @sql = 'DECLARE @tblName varchar(128), @sqlInline nvarchar(2000), @postFix varchar(2)

 SET @postFix = ''_V''

 SET @tblName = ''?''

 IF @tblName LIKE ''%_INCR]''
  
 BEGIN
  
  SET @sqlInline = ''TRUNCATE TABLE '' + @tblName + '';

   TRUNCATE TABLE '' + SUBSTRING(@tblName, 1, LEN(@tblName)-6) + @postFix + '']''
    
  EXEC sp_executesql @sqlInline
  
 END
 '
 
 EXEC sp_MSForEachTable @command1 = @sql
END

Notes

The script works for NAV Adapters. If you want to use this for a normal data source, you must replace %_INCR] with %_I]

If the script is used for other data sources than NAV, you should also change the parameter from 6 to 3 in the LEN-part so the entire line reads as follows:

TRUNCATE TABLE '' + SUBSTRING(@tblName, 1, LEN(@tblName)-3) + @postFix + '']''

This article applies to: timeXtender 4.5, tX2012, TX2014.

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

13 Comments

  • 0
    Avatar
    Tobias Eld

    Thank you very much for this addition, I have included it in the original post above.

  • 0
    Avatar
    Thomas Lørup Duun

    IF DATEADD(d,0,DATEDIFF(d,0,GETDATE()))=DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

     

  • 0
    Avatar
    Teddy N. Jørgensen

    I have some tables where above sript are running and it works fine, but If you would like to make the full load only once per month, for example the last day of the month, how would it then have to look like? 

  • 0
    Avatar
    Morten Søndergaard

    The tricky part must be finding the last day of the month.

    Pinal Dave has the answer :

    h ttp://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

  • 0
    Avatar
    Bas Bruil

    To make sure that Sunday = 1, so this line will work as expected:

    DATEPART(dw,GETDATE())= 7 -- 7 is Saturday, 1 is Sunday

     

    I think you should start the script with:   SET DATEFIRST 7 (where 7 is a Sunday)

    Otherwise you might end up performing full loads on other days then expected.. 

     

  • 0
    Avatar
    Steffen Eriksen

    Are you able to give an exact example - if you for instance have these 2 tables where you have used incremental load: NAV_dbo_Sales Invoice Line_?, NAV_dbo_Purchase Invoice Line_?

  • 0
    Avatar
    Thomas Lørup Duun

    The script would often be run in a IF scope to make a full load on certain days, for example:

    IF DATEPART(dw,GETDATE())= 7 -- 7 is Saturday, 1 is Sunday

    BEGIN

    -- Your Truncation Script here

    END

    Change the datepart parameter to match your requirements, ie hh for hours (See more paramters here:  http://msdn.microsoft.com/en-us/library/ms174420.aspx )

  • 0
    Avatar
    Thomas Lørup Duun

    The script will truncate the _V and _INCR table for any table with source based incremental load enabled. 

    If you want a specific table full loaded and not all, then you can simply truncate the Valid table and Incremental table for that particular table.

     

    i.e

    Truncate Table [NAV_dbo_Sales Invoice Line_V]

    GO

    Truncate Table [NAV_dbo_Sales Invoice Line_INCR]

    GO

  • 0
    Avatar
    Steffen Eriksen

    Hi Thomas

    Thank you much for the explanation - will give it a try :)

  • 0
    Avatar
    Morten Søndergaard

    Perfect! Thanks.

  • 0
    Avatar
    Morten Søndergaard

    I the script is used for other datasources than NAV, the remember to correct the parameter from 6 to 3 in the LEN-part:

    TRUNCATE TABLE '' + SUBSTRING(@tblName, 1, LEN(@tblName)-3) + @postFix + '']''

  • 0
    Avatar
    Morten Søndergaard

    Where will i put this script?

    • Stored Procedures

    • User defined functions

    • Script Actions

  • 0
    Avatar
    Tobias Eld

    You add the script as a Script Action, then you right click the business unit, go to Advanced and select Set Pre and Post Scripts. Then you can select the script in the pre section (first dropdown). This will execute the script before the business unit is executed.

Please sign in to leave a comment.