0

I have these two temp tables that I'm using (they were actually written by a previous developer and I'm trying to adapt his code to be faster without using cursors, but that's beside the point) and I've run into a little problem that I can't figure out.

Here's some code:

CREATE TABLE #bc1 (
         [ContractID] decimal
        ,[custid] int
        ,[PostDate] date
        ,[RouteID] varchar(50)
        ,[RouteCommission] money
        ,[ContractTotal] money
        ,[ContractTotal0] money
    )

    INSERT INTO #bc1 ( [ContractID], [custid], [PostDate], [RouteID], [RouteCommission] )
    SELECT
         t.[ContractID]
        ,t.[custid]
        ,t.[PostDate]
        ,t.[RouteID]
        ,t.[RouteCommission]
    FROM 
        #tc1 t;

Now, as you can see it's pretty simple. There's a table #bc1 being created with five columns, and it gets all of the data from another table which has 7 columns. This is pretty much smack in the middle of the query (which is about four pages, which is why I didn't include it) and this is where it breaks. It tells me

Msg 207, Level 16, State 1, Line 178
Invalid column name 'custid'.

and it's driving me nuts. Incidentally, line 178 is the insert statement.

Any ideas?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Corran Horn
  • 161
  • 1
  • 10
  • 5
    Are you sure the `#tc1` table has a `custid` field? – SWeko Dec 28 '12 at 15:02
  • 2
    And does the table `#tc1` has a column `custid`? – Lamak Dec 28 '12 at 15:02
  • The create statement is for #bc1, but the select expects the custid column in table #tc1 – Andomar Dec 28 '12 at 15:06
  • As a bonus comment (and because I lothe cursors), have a look here for alternatives ;) http://stackoverflow.com/questions/5425642/what-is-an-alternative-to-cursors-for-sql-looping – JOpuckman Dec 28 '12 at 15:07
  • #tc1 does have a custid column. I think the issue is the fact that the temp table was never dropped. I dropped the temp table before creating it, we'll see how it goes. – Corran Horn Dec 28 '12 at 15:16
  • Can you post the definition of #tc1? Also, sometimes the line number for an error can be off quite a bit. You could temporarily put an additional insert in the empty line that references a not existing table to see what line number you get for that. If that is not 177 your original error is somewhere else too. – Sebastian Meine Dec 28 '12 at 15:19
  • I would use "IF OBJECT_ID('tempdb..#tc1') IS NOT NULL DROP TABLE #tc1" and "IF OBJECT_ID('tempdb..#bc1') IS NOT NULL DROP TABLE #bc1" at the beginning of the query. – Neil Dec 28 '12 at 17:09
  • Are there any triggers or dynamic code that operate on the temp tables? If you're trying to get away from the cursors consider APPLY and CROSS APPLY. – Volvox Jan 01 '13 at 01:05

1 Answers1

-1
 Can you try 'SELECT * FROM #tc1' as a test (without inserting into #bc1 table).
Narsimha
  • 184
  • 4