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

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;

CodePudding user response：

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;

CodePudding user response：

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.