CODEWITHSUNDEEP
sqlsql-servert-sqlglobal-temp-tables
Mathematics
Mathematics

Reputation: 7628

Sum Over SQL without grouping or ordering

I have a SQL Server query like this:

CREATE TABLE ##Temp(
    OrderID NVARCHAR(100), 
    ID INT,
    Prices INT,
    Total INT
);

INSERT INTO ##Temp (ID, Prices, OrderID, Total)
SELECT  fc.ID, f.Prices, f.OrderID, 
        (SUM(f.Prices) OVER()) AS Total 
FROM FruitCrates fc
LEFT JOIN Fruits f ON fc.ID = f.FruitCrateID
WHERE  fc.OrderID LIKE '18_1635' 
  AND fc.Rights = 1 
  AND fc.Cancelled = 0 
  AND f.OrderID IS NOT NULL;

SELECT * FROM ##Temp;

But I keep getting an error:

Msg 207, Level 16, State 1, Line 12
Invalid column name 'Total'.

Which I think is because I am not using OVER() properly BUT I am not sure how to fix it.

PLEASE NOTE

SELECT statement works on its on when not put in after INSERT.

Results without INSERT

╔═════╦════════╦══════════╦═══════╗
║ ID  ║ Prices ║ OrderID  ║ Total ║
╠═════╬════════╬══════════╬═══════╣
║  77 ║      1 ║ 1_370    ║   104 ║
║  19 ║    101 ║ 1811_171 ║   104 ║
║  77 ║      2 ║ 1811_171 ║   104 ║
╚═════╩════════╩══════════╩═══════╝

Upvotes: 3

Views: 86

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

Error message with insert Invalid column name 'Total'. indicates clearly that ##Temp table has different schema than column list in INSERT INTO (...) statement.

To troubleshoot it you should always check metadata(column names) right before insert:

SELECT *
FROM ##Temp
WHERE 1=2;

Then you could easily spot that it is different that what you've assumed to be.

Now you are using global temp table, it will live as long as last connection that refer to it is active. You should consider using local temp tables.

To solve your case you could:

  1. Use different name to avoid collision

  2. Right before you could (if not used in nested procedures):

    IF OBJECT_ID('tempdb..##temp') IS NOT NULL     
DROP TABLE ##temp

CREATE TABLE ##temp...

There is also one nasty case when stored procedure called another stored procedure and both (outer and inner) creates temp table with the same name. You should avoid it.

CREATE PROCEDURE #outer
AS
BEGIN
   CREATE TABLE ##temp (id INT, guid UNIQUEIDENTIFIER, col2 VARCHAR(100));
   INSERT INTO ##temp VALUES(1,NEWID(), 'a');
   SELECT * FROM ##temp;
   EXEC [#inner];
END
GO

CREATE PROCEDURE #inner
AS
BEGIN
    CREATE TABLE ##temp (id INT, total INT);  -- no error during creation

    INSERT INTO ##temp(id, total)
    SELECT 2, 10;

    SELECT * FROM ##temp;
END
GO

EXEC #outer
-- Invalid column name 'total'.

LiveDemo

The funniest thing is when you use local temp tables with the same number of columns(or defaults) and implicit casts are possible, it will pass:

CREATE PROCEDURE #outer
AS
BEGIN
CREATE TABLE #temp (id INT, col2 varchar(10));
   INSERT INTO #temp VALUES(1, 'a');
   SELECT * FROM #temp;
   EXEC [#inner];
END
GO

CREATE PROCEDURE #inner
AS
BEGIN
    CREATE TABLE #temp (id INT, total INT);
    INSERT INTO #temp
    SELECT 2, 10;

    SELECT * FROM #temp;
END
GO

EXEC #outer

LiveDemo2

And example with incompatible types:

CREATE PROCEDURE #outer
AS
BEGIN
CREATE TABLE #temp (id INT, col2 UNIQUEIDENTIFIER);
INSERT INTO #temp VALUES(1, NEWID());
   SELECT * FROM #temp;
   EXEC [#inner];
END
GO

CREATE PROCEDURE #inner
AS
BEGIN
    CREATE TABLE #temp (id INT, total INT);
    INSERT INTO #temp
    SELECT 2, 10;

    SELECT * FROM #temp;
END
GO

EXEC #outer

Operand type clash: int is incompatible with uniqueidentifier

Upvotes: 1

Related Questions