Why is this temporary table throwing an error about the number of column supplied?

user_18091998 0
Viewed 63

I'm trying to run this specific code for a temp table, but somehow I get this error

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

What's wrong?

DROP TABLE IF EXISTS #GamesDistribution

CREATE TABLE #GamesDistribution 
(
    Platform nvarchar(255),
    Name nvarchar(255),
    NA_Sales numeric,
    EU_Sales numeric,
    JP_Sales numeric
)

INSERT INTO #GamesDistribution
    SELECT 
        properties.Platform,
        properties.Name,
        revenue.NA_Sales,
        revenue.EU_Sales,
        revenue.JP_Sales
    FROM 
        games_properties AS Properties
    JOIN 
        games_revenue AS Revenue ON properties.Game_ID = Revenue.Game_ID
    --GROUP BY properties.platform
    --ORDER BY Total_Games DESC, Total_NA_Sales DESC, Total_EU_Sales DESC, Total_JP_Sales DESC;
tsql sql-server sql
3 Answers

The problem here is that prior to you running your batch the table already exists. As such when the batch is parsed, by the compiler, the compilation fails; because the number of columns doesn't match that of the table already exists.

This can be replicated with the following:

CREATE TABLE #t (I int);

INSERT INTO #t (I)
VALUES(1);

GO
DROP TABLE IF EXISTS #t;
CREATE TABLE #t (I int, D date);

INSERT INTO #t
VALUES(2,GETDATE());
GO

SELECT *
FROM #t;
GO
DROP TABLE #t

db<>fiddle

This returns the error:

Msg 213, Level 16, State 1, Line 10
Column name or number of supplied values does not match table definition.

And the dataset:

I
1

This is because the 2nd batch, with the DROP TABLE IF EXISTS never ran; the compilation failed.

The "simple" solution here would be to put your DROP IF EXISTS in a separate batch, and also specify your columns:

DROP TABLE IF EXISTS #GamesDistribution;
GO
CREATE TABLE #GamesDistribution (Platform nvarchar(255),
                                 Name nvarchar(255),
                                 NA_Sales numeric,  --Where is your precision and scale?
                                 EU_Sales numeric,  --Where is your precision and scale?
                                 JP_Sales numeric); --Where is your precision and scale?

INSERT INTO #GamesDistribution (Platform,Name, NA_Sales,EU_Sales,JP_Sales)
SELECT properties.Platform,
        properties.Name,
        revenue.NA_Sales,
        revenue.EU_Sales,
        revenue.JP_Sales
FROM dbo.games_properties AS Properties
     JOIN dbo.games_revenue AS Revenue ON properties.Game_ID = Revenue.Game_ID;
user_20299830

You can actually do this way

DROP TABLE IF EXISTS #GamesDistribution

SELECT properties.Platform,
        properties.Name,
        revenue.NA_Sales,
        revenue.EU_Sales,
        revenue.JP_Sales
INTO #GamesDistribution
FROM games_properties AS Properties
JOIN games_revenue AS Revenue
    ON properties.Game_ID = Revenue.Game_ID

and then you can check the columns' data types of the temp table:

EXEC tempdb..sp_help '#GamesDistribution'

SELECT * 
    FROM tempdb.sys.columns 
    WHERE [object_id] = OBJECT_ID('tempdb..#GamesDistribution');

Note: It's always better to ensure the columns' data types. Your query might list different columns' data types.

user_12769150

Add GO statement under drop table as below.

DROP TABLE IF EXISTS #GamesDistribution
GO

CREATE TABLE #GamesDistribution 
( 
.
.
.
user_6737070
Related