CODEWITHSUNDEEP
sql-servert-sql
unknown
unknown

Reputation: 461

Temp tables, Column name or number of supplied values does not match table definition

Even though this tends to look as a duplicate, I had to post it as I can't seem to spot the error.

I don't know if I am mad or what but I can't seem to spot why there is a mismatch in the number of supplied values.

Here are they:

   CREATE TABLE #TIPSTOPE_TS
        (
          TIP INT NULL,
          SIFVAL VARCHAR(5),
          GRUPA INT NULL,
          DATUMOD VARCHAR(15),
          PASIVNA DECIMAL(15,4) NULL DEFAULT(0),      
          REDOVNA DECIMAL(15,4) NULL DEFAULT(0),      
          ZATEZNA DECIMAL(15,4) NULL DEFAULT(0), 
          STOPA DECIMAL(15,4) NULL DEFAULT(0),
          DATUMDO VARCHAR(15),
          KONTO VARCHAR(15),


        )
         INSERT INTO #TIPSTOPE_TS
         SELECT TS.TIP,
                TS.SIFVAL,
                TS.GRUPA,
                CASE WHEN ISDATE(MAX(TS.DATUMOD)) = 0 THEN '2017.12.31' ELSE MAX(TS.DATUMOD) END AS DATUMOD,                           
                      CAST (2 AS DECIMAL(10,4)) AS PASIVNA,                           
                      CAST (1 AS DECIMAL(10,4)) AS REDOVNA,                           
                      CAST (3 AS DECIMAL(10,4)) AS ZATEZNA,
                        TS.REDOVNA,  
                        TS.DATUMDO,  
                        TP.M1 AS KONTO
         FROM TIPSTOPE TS WITH(NOLOCK)
         JOIN TIPPART TP WITH(NOLOCK) ON TP.TIP = TS.TIP
         WHERE TS.DATUMOD <= '2017.12.31'
         GROUP BY TS.TIP,TS.SIFVAL,TS.GRUPA,TP.M1,TS.DATUMDO,TS.REDOVNA

         CREATE NONCLUSTERED INDEX IX_TIPSTOPE_TS ON #TIPSTOPE_TS (TIP, GRUPA, SIFVAL)
                INCLUDE (DATUMOD)

And the second one...

 CREATE TABLE #UNPVT_TIPSTOPE_TS      
            (      
                  TIP INT NULL,        
                  SIFVAL VARCHAR(5) NULL,       
                  GRUPA INT NULL,       
                  DATUMOD VARCHAR(10) NULL,       
                  TIP_KS VARCHAR(15) NULL,       
                  KAMATNA_STOPA DECIMAL(15,4) NULL DEFAULT(0),
                  DATUMDO VARCHAR(10) NULL,  
            )           

            INSERT INTO #UNPVT_TIPSOPE_TS      
            SELECT TIP, SIFVAL, GRUPA, DATUMOD, TIP_KS, KAMATNA_STOPA,DATUMDO   
            FROM         
            (      
                  SELECT TIP, SIFVAL, GRUPA, DATUMOD, ISNULL(REDOVNA,0) AS REDOVNA, ISNULL(PASIVNA,0) AS PASIVNA, ISNULL(ZATEZNA,0) AS ZATEZNA,STOPA,DATUMDO
                  FROM #TIPSTOPE_TS      
            ) P      
           UNPIVOT (KAMATNA_STOPA FOR TIP_KS IN (REDOVNA, PASIVNA, ZATEZNA)) AS UNPVT

The second temp tables is taking data from the first one.

When I try to create the second one error is thrown:

Insert error: Column name or number of supplied values does not match table definition

Upvotes: 2

Views: 8236

Answers (1)

gotqn
gotqn

Reputation: 43626

You are specifying the exact number of values that are needed. If you copy the whole code in new query window and execute it, it will work. Or in your current window drop the table table:

DROP TABLE #TIPSTOPE_TS;
DROP TABLE #UNPVT_TIPSTOPE_TS;

I mean execute only the above statements, and the execute the rest of the code. It should work again.

Sometime, when are debugging we forgot that the temporary table meta data is cached. For example, you can have the following code:

DROP TABLE IF EXISTS #TEST;

CREATE TABLE #TEST
(
    [A] INT
);

INSERT INTO #TEST ([A])
SELECT 1;

And its valid. If we change it to this:

DROP TABLE IF EXISTS #TEST;

CREATE TABLE #TEST
(
    [A] INT
   ,[B] INT
);

INSERT INTO #TEST ([A], [B])
SELECT 1, 2;

We will get:

Msg 207, Level 16, State 1, Line 9 Invalid column name 'B'.

Because, in the current session the #TEST table already exists and the engine is able to check that the B column does not exists. So, we need to drop the table manually, after the columns are changed, or we need to drop the tables at the end of our code statements.

Upvotes: 4

Related Questions