CODEWITHSUNDEEP
sqlsql-server-2005subqueryalias
Anchit
Anchit

Reputation: 558

using alias to reference result set obtained by unions in a subquery

I'm having trouble with this query and can't seem to find a solution on the net. What I'm trying to do is to select rows from several tables after applying unions on them and then insert only those rows into table1, whose col2, and col3(as a pair) are not already present in table1.

INSERT INTO [somedbo].[table1](col1, col2, col3, col4) 
    SELECT temptbl.MAX(col1), temptbl.col2, temptbl.col3, (temptbl.col2+' '+temptbl.col3)
      FROM    (
               .
               .
             //sub query with unions. which is working fine.
               .
               .
              ) AS temptbl
      WHERE  not exists(select 1 from temptbl as t where t.col2=table1.col2 AND t.col3=table1.col3)
      GROUP  BY col2, col3
      ORDER  BY col2, col3 ASC

I'm using SQL Server 2005, and I'm getting the error: Msg 208, Level 16, State 1, Line 1 Invalid object name 'temptbl'.

Thanks in advance for helping me out.

Upvotes: 0

Views: 3032

Answers (2)

Axn
Axn

Reputation: 356

Use the WITH clause to create a Common Table Expression. http://msdn.microsoft.com/en-us/library/ms175972.aspx

Upvotes: 1

JNK
JNK

Reputation: 65157

You can't reference your named subquery temptbl in your WHERE clause subquery.

Use table variable instead:

DECLARE @tmptable TABLE (<table definition>)

INSERT INTO @tmptable
SELECT...
<UNION QUERY>

Then use the @tmptable in your query as you are now.

Upvotes: 2

Related Questions