CODEWITHSUNDEEP
sql-servert-sqltemp-tablesinsert-into
Mike
Mike

Reputation: 1261

T-SQL error when trying to insert into a temp

I have a temp table I'm trying to populate from a select statement.

However I'm getting this error :

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

I have tracked it down to the column endtime that using the LEAD; if I removed this LEAD column, would it work?

Not sure what I'm missing

Thanks Mike

IF OBJECT_ID('tempdb.dbo.#flextx', 'U') IS NOT NULL
     DROP TABLE #flextx; 

 CREATE TABLE dbo.#flextx
 (
     OmanId VARCHAR(255),
     MRDF VARCHAR(255), 
     status VARCHAR(255), 
     statusid BIGINT, 
     EVENT_ACTION BIGINT,
     starttime DATETIME,
     endtime DATETIME
 ) 

INSERT INTO dbo.#flextx
    SELECT 
        f.OmanId, 
        f.MRDF, 
        s.status, 
        s.statusid, 
        f.EVENT_ACTION, 
        (f.TIMESTAMP) AS starttime,
        (LEAD(f.TIMESTAMP, 1, GETDATE()) OVER (ORDER BY f.TIMESTAMP)) AS endtime 
    FROM  
        dbo.#flext f 
    LEFT JOIN  
        dbo.statues AS s ON s.flexstatus = f.JOB_STATUS_TEXT
    WHERE 
        EVENT_ACTION IN (225, 226)  
        AND s.statusid = 23 
    ORDER BY 
        MRDF, EVENT_ACTION

Upvotes: 1

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

This is a compile-time error. Add a GO after the CREATE TABLE statement.

What is happening is that you are redefining the table. The old definition is used when the script is compiled. You should also be able to fix this by just deleting the existing version of the table.

Upvotes: 3

Hogan
Hogan

Reputation: 70523

you have a typo I think you want endtime to be the next start time that would be like this:

     (f.starttime) AS starttime,
      ( LEAD(f.starttime,1,GetDate()) OVER (ORDER BY mrdf, event_action) ) AS endtime

there is no TIMESTAMP column

Upvotes: 0

Related Questions