TSQL: WITH cteTABLE insert into other_table

Question

Possible Duplicate:
Incorrect syntax near the keyword 'with'…previous statement must be terminated with a semicolon

I want to select hierachical data and insert it in a table. Therefore i need to use the WITH statment in my insert.

This works fine:

create table test_table
(
 id int
)   

with t_table 
as
(select 12345 wert)
insert into test_table (id)
select wert from t_table

But this generates a "wrong syntax near WITH keyword" error:

CREATE PROCEDURE p_insert_test
AS
BEGIN

with t_table 
as
(select 12345 wert)
insert into test_table (id)
select wert from t_table

END

I guess T-SQL doesn't like the WITH keyword before the INSERT keyword. How can i do this kind of insert in a stored procedure?

Thank you!

Answer 1

The CTE declaration needs to be the first command in the batch.

Just put a semicolon before the WITH and you should be fine:

;WITH t_Table...

EDIT:

Just to clarify why this is the case, the WITH keyword is used for query hints as well, such as WITH RECOMPILE, WITH (NOLOCK), etc. The query engine needs to know the intent of the WITH keyword, and the only way to signify that it represents a CTE is to ensure that the CTE declarations are the beginning of the batch.

Otherwise, you could have something ambiguous like:

SELECT Field
FROM MyTable
WITH CteName
As
(Select 1)

Without the statement terminator, CteName will be inappropriately read as a query hint applied to MyTable.