How to add a temp table in SQL server

Question

I am trying to add a temp table to my query so that I can query that temp table, I have searched the internet but I couldn't get a solution. this is my query

;WITH cte AS (
        SELECT ID, g.Name
        FROM game.Game g WITH(NOLOCK
        WHERE ID IN (SELECT Data FROM system.Split(1, ','))
        UNION ALL 
        SELECT g.ID,  g.Name
        FROM game.Game g WITH(NOLOCK) 
            JOIN cte ON g.ParentID = cte.ID
    )

    SELECT  c.ID,
        c.Name

    FROM cte c
        INNER JOIN list.Type gt WITH(NOLOCK) ON c.TypeId = gt.TypeID

    WHERE c.ID NOT IN (SELECT Data FROM system.Split(1, ','))
        AND c.ID IN (SELECT ID FROM game.code WITH(NOLOCK)
        WHERE ID = c.ID
            AND StatusCode IN ('OP', 'CL', 'SU')
            AND isDisplay = 'True' 
            AND GETDATE() BETWEEN DisplayStart AND DisplayEnd
            AND GETDATE() < ISNULL(ResultDateTime, ResultExpected) 
        )

which gives me the following when I run it

ID    | Name
1111  | BaseBall
2222  |BasketBall
45896 |Relay

now I tried to create a temp table as follows

Create Table #temp(
        ID int,
        Name varchar
        )
        ;WITH cte AS (
        SELECT ID, g.Name
        FROM game.Game g WITH(NOLOCK)
        WHERE ID IN (SELECT Data FROM system.Split(1, ','))
        UNION ALL 
        SELECT g.ID,  g.Name
        FROM game.Game g WITH(NOLOCK) 
            JOIN cte ON g.ParentID = cte.ID
    )
    insert into #temp  // i wanted to set these values in the temp table
    SELECT  c.ID,
        c.Name

    FROM cte c
        INNER JOIN list.Type gt WITH(NOLOCK) ON c.TypeId = gt.TypeID

    WHERE c.ID NOT IN (SELECT Data FROM system.Split(1, ','))
        AND c.ID IN (SELECT ID FROM game.code WITH(NOLOCK)
        WHERE ID = c.ID
            AND StatusCode IN ('OP', 'CL', 'SU')
            AND isDisplay = 'True' 
            AND GETDATE() BETWEEN DisplayStart AND DisplayEnd
            AND GETDATE() < ISNULL(ResultDateTime, ResultExpected) 
        )

every time I try to store this information in the temp table it gives me an error 'Column name or number of supplied values does not match table definition.' But I only have two values in. What am I doing wrong that I cant see?

What is length of varchar column in Temp table u have given ? — Ven
– Ven, Commented May 11, 2018 at 14:42
To expand and @Larnu's comment...blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere And do you have a column named 1? That split function looks pretty suspect to me. — Sean Lange
– Sean Lange, Commented May 11, 2018 at 14:54

Yogesh Sharma · Accepted Answer · 2018-05-11 14:53:40Z

2

First, why not just use select into?

IF OBJECT_ID('TempDB..#temp') IS NOT NULL  
BEGIN
    DROP TABLE #temp
END
select c.ID, c.Name
into #temp
from . . .

Then you don't need to define #temp as a table.

Next, your definition is bad, because Name has only one character. This would be fixed with select into.

However, I don't know why you are getting the particular error you are getting. The numbers of columns appears to match.

edited May 11, 2018 at 14:53

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

answered May 11, 2018 at 14:39

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yogesh Sharma Over a year ago

Be aware while executing this as in same session it will throw error.

Collectives™ on Stack Overflow

How to add a temp table in SQL server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related