Invalid column name during Insert Into

Question

I can't for the life of me figure out why I get an error of "Invalid column name 'AvgDaily'" on the following code. Any ideas?

create table #TMP_Daily_Avg_Rollup 
(
     Zone varchar(20), 
     fkiItem int, 
     fkiLocation int, 
     AvgDaily decimal(12,3)
)

Insert into #TMP_Daily_Avg_Rollup (Zone, fkiItem, fkiLocation, AvgDaily)
    Select
        tte.Zone, tte.fkiItem, tte.fkiLocation, 
        (sum(tte.TransQty) / @UsageDaysLong) as AvgDaily
    From
        #TMP_Trans_Events tte
    Group By 
        tte.Zone, tte.fkiItem, tte.fkiLocation
    Order By 
        tte.fkiItem

What's the exact error message? http://stackoverflow.com/help/how-to-ask — Tom H, Mar 15 '18 at 20:43
Cannot reproduce your case [demo](http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=554aedba8c1e3b139f090ac216141d3a) — Lukasz Szozda, Mar 15 '18 at 20:44
Using MS-SQL Error message is Msg 207, Level 16, State 1, Line 154 Invalid column name 'DailyAvg'. — Bob Shabowski, Mar 15 '18 at 20:45
DailyAvg != AvgDaily. There is no `DailyAvg` in your snippet — Lukasz Szozda, Mar 15 '18 at 20:45
Your script doesn't have column `DailyAvg`. So where's the error coming from????? — Eric, Mar 15 '18 at 20:52
Sorry, been trying other names just in case. The error was for AvgDaily — Bob Shabowski, Mar 15 '18 at 21:00
I can't identify the error, but you could try dropping the temp table then writing: `SELECT ... INTO #TMP_Daily_Avg_Rollup FROM...` instead. — Zorkolot, Mar 15 '18 at 21:46
Does this answer your question? ["Invalid column name" error when calling insert after table created](https://stackoverflow.com/questions/16165803/invalid-column-name-error-when-calling-insert-after-table-created) — argonym, Feb 20 '20 at 18:35

score 0 · Answer 1 · edited Mar 15 '18 at 20:49

0

very wild guess

Lose the outer parentheses marks, column aliases in your SELECT, and table alias as we're only selecting from one table

INSERT INTO #TMP_Daily_Avg_Rollup (Zone, fkiItem, fkiLocation, AvgDaily)
SELECT Zone, fkiItem, fkiLocation, sum(TransQty) / @UsageDaysLong
FROM #TMP_Trans_Events 
GROUP BY Zone, fkiItem, fkiLocation
ORDER BY fkiItem

As an aside..

Do you really need the ORDER BY to insert rows into your temp table?
What's the column data type of TransQty and UsageDaysLong? If you expect an expression return values with three decimal places you'll want to make sure that one of those values also has three decimal places.

edited Mar 15 '18 at 20:49

answered Mar 15 '18 at 20:46

Jim Horn

879
6
14

Qualified column names are a best practice. This is really bad advice and has nothing to do with the question, given that `AvgDaily` is not even selected in the `SELECT` portion of the query. – Gordon Linoff Mar 15 '18 at 20:53
Qualified column names in a SELECT are not relevant when the overall query is an INSERT, as the ordering of column position between the INSERT and SELECT is enforced. – Jim Horn Mar 15 '18 at 20:55
TransQty is a Decimal and UsageDaysLong is a tinyint – Bob Shabowski Mar 15 '18 at 20:57
When you click on the error message the cursor will jump to the offending line. Which line does it jump to, the CREATE TABLE, or the INSERT? – Jim Horn Mar 15 '18 at 21:05
It jumps to the Insert line – Bob Shabowski Mar 15 '18 at 21:06
Hmm. Not seeing anything that would cause this error message. Please re-post your current code. – Jim Horn Mar 15 '18 at 21:12

Invalid column name during Insert Into

1 Answers1