Using the WITH clause in an INSERT statement

Question

I was wondering if this was possible. I have an existing query that uses the WITH clause to apply some aggregated data to a SELECT query like so: (massively simplified)

;WITH alias (y,z)
AS
(
    SELECT y,z FROM tableb
)
SELECT y, z FROM alias

I now want to INSERT the results of this query into another table.

I have tried the following:

INSERT INTO tablea(a,b)
;WITH alias (y,z)
AS
(
    SELECT y,z FROM tableb
)
SELECT y, z FROM alias

but I get the error:

Incorrect syntax near ';'.

So I have tried without the semicolon but got the error:

Incorrect syntax near the keyword 'WITH'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Is what I am trying to do possible with different some different syntax?

The semicolon goes at the **end** of a statement. Putting it at the front is a bad habit and you should get used to properly terminating every statement with a semicolon. — , Mar 22 '13 at 15:40
Did you check the [documentation](http://msdn.microsoft.com/en-us/library/ms174335(v=sql.110).aspx) for the `INSERT` statement? It shows that the CTE comes before the `INSERT`, and includes an example of using a CTE. — Pondlife, Mar 22 '13 at 16:26

score 86 · Accepted Answer · answered Mar 22 '13 at 15:38

86

You will need to place the INSERT INTO right after the CTE. So the code will be:

;WITH alias (y,z)
AS
(
    SELECT y,z FROM tableb
)
INSERT INTO tablea(a,b)
SELECT y, z 
FROM alias

See SQL Fiddle with Demo

answered Mar 22 '13 at 15:38

Taryn

242,637
56
362
405

score 1 · Answer 2 · answered Mar 22 '13 at 15:41

1

Another way without using a CTE is by wrapping it in a subquery,

INSERT INTO tablea(a,b)
SELECT y, z 
FROM 
(
    SELECT y,z FROM tableb
) alias

answered Mar 22 '13 at 15:41

John Woo

258,903
69
498
492

@KSTech good question. I was thinking that the OP was just simlifying the problem. – John Woo Mar 22 '13 at 15:48

score 0 · Answer 3 · answered Sep 12 '13 at 13:02

Semicolon is used to terminate the statement. So when you use ;WITH, terminates the previous statement. However, that's not why you are getting the error here. The problem here is with your INSERT INTO statement, which is looking for VALUES or SELECT syntax.

INSERT INTO statement can be used in 2 ways - by providing VALUES explicitly or by providing a result set using SELECT statement.

score 0 · Answer 4 · answered Sep 05 '22 at 12:28

In my case the suggested answer was unappliable, I could think it is a metter of SQL Server Version which in my case is SQL Server 2016. Alternatively you could use temp tables through this snippet of code:

;WITH alias (y,z)
AS (SELECT y,z FROM tableb)
SELECT Y,Z
INTO #TEMP_TABLE
FROM alias

Z

Using the WITH clause in an INSERT statement

4 Answers4