CTE and dynamic sql instead of temporary table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CTE and dynamic sql instead of temporary table

Author

Topic

Jayaraj
Starting Member

3 Posts

Posted - 2007-02-27 : 12:59:22

I am trying to use dynamic sql with CTE, but couldn't.Please help
I want to use CTE instead of temporary tables. I know that I can use temp table here, but I was looking for a way to use CTE

DECLARE @ora AS VARCHAR(4000)
SET @ora = ' SELECT * FROM OPENQUERY(oracle_linked_server,
''SELECT EMPLOYEE_ID , OFFICE,EMPLOYEE_POSITION, EMPLOYEE_NAME,EMAIL
FROM EMPLOYEE_TABLE '' )' ;

WITH TEMP_EMPLOYEE(EMPLOYEE_ID , OFFICE,EMPLOYEE_POSITION, EMPLOYEE_NAME,EMAIL) AS
(
EXEC(@ora)
)

Thanks,
Jay

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-27 : 14:25:18

have you looked in bol?
the CTE definition has to be a select statement.

you should be able to

;WITH TEMP_EMPLOYEE(EMPLOYEE_ID , OFFICE,EMPLOYEE_POSITION, EMPLOYEE_NAME,EMAIL) AS
(
SELECT * FROM OPENQUERY(oracle_linked_server,
'SELECT EMPLOYEE_ID , OFFICE,EMPLOYEE_POSITION, EMPLOYEE_NAME,EMAIL
FROM EMPLOYEE_TABLE ' )
)

or put the whole statement in dynamic sql.
Unfortunately openquery doesn't take an expression as the query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources