Loading time dimension table t-sql

Question

I am trying to (synthetically) populate an empty time dimension table in a star modeled data mart, after which it should look like this:

I am using the following T-SQL code for this:

    /*
STEP 3
Populate DIM_TIJD table with date and its derrived values data
*/
USE Fuzzy_DM_Robin
--DECLARE DATE VARIABLES FOR DATE PERIOD
DECLARE @StartDate datetime = '01/01/1995'
DECLARE @EndDate datetime   = '01/01/2026'
DECLARE @DateInProcess datetime
SET @DateInProcess      = @StartDate
WHILE @DateInProcess    < = @EndDate
BEGIN
    SET NOCOUNT ON
--LOOP THROUGH INDIVIDUAL DATES DEFINED BY TIME PERIOD
        INSERT INTO DIM_TIJD (
        [DATUM_ID],
        [DATUM],
        [DAG_VD_WEEK],
        [WEEKNR],
        [MAAND],
        [MAAND_OMSCHRIJVING],
        [LAATSTE_DAG_MAAND],
        [KWARTAAL],
        [JAAR]
        )
        VALUES (
        CAST ( @DateInProcess AS numeric (10) ),
        @DateInProcess,
        CONVERT(varchar(10), @DateInProcess, 110) + ', ' + DATENAME(WEEKDAY, @DateInProcess ),
        DATEPART (wk, @DateInProcess),
        MONTH( @DateInProcess),
        CAST(YEAR(@DateInProcess) as varchar(4)) + ' - ' + DATENAME(MONTH, @DateInProcess ),
        DATEPART (dd, EOMONTH ( @DateInProcess)),
        DATENAME( QUARTER, @DateInProcess ),
        YEAR(@DateInProcess))
END

Nevertheless, it won't load. Messages I receive are:

Message 1:

String or binary data would be truncated

and message 2:

[Execute SQL Task] Error: Executing the query "/* STEP 3 Populate DIM_TIJD table with date and it..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

What am I doing wrong? There are no under linings in SSMS indicating that something is wrong with the code and I have checked all the varchar lengths to be the same for both the table and the code. I have tried fiddling around with result set settings, but that only gives me another message:

Message 3:

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

Thank you in advance!

score 0 · Answer 1 · answered Apr 30 '16 at 18:36

0

The @DateInProcess variable needs to be incremented as part of the loop.

Try adding this as the last line of your loop (before END):

SET @DateInProcess = DATEADD(DAY, 1, @DateInProcess)

answered Apr 30 '16 at 18:36

Chris Mack

5,148
2
12
29

Hello Chris, thanks for your input. Unfortunately it doesn't help, the same problem persists...any additional suggestions? – R. Neven May 02 '16 at 09:10
I would start by checking your columns in the table with regards to their data types and lengths - is the data going to fit into the table or is it going to be truncated? I'd recommend setting up the scenario with a temp table or table variable and seeing if that works. Feel free to post the table design/data types here and I'll take a look. – Chris Mack May 02 '16 at 17:29

Loading time dimension table t-sql

1 Answers1