CODEWITHSUNDEEP
sqlsql-servercommon-table-expressionwith-clause
Macs Dickinson
Macs Dickinson

Reputation: 987

Using the WITH clause in an INSERT statement

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?

Upvotes: 50

Views: 111135

Answers (6)

Paolo
Paolo

Reputation: 627

In my case the suggested answer was inapplicable, I could think it is a matter 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

Upvotes: 0

Steven Cromb
Steven Cromb

Reputation: 61

For those that have landed here while trying to solve the same problem for MySQL, the INSERT statement has to go before the CTE:

-- MySQL code! Not SQL Server
INSERT INTO tablea(a,b)
WITH alias AS
(
    SELECT y,z FROM tableb
)
SELECT y, z 
FROM alias

Upvotes: 0

Franco Gil
Franco Gil

Reputation: 429

on db2 is possible.

Disclaimer

I will be using a complete different example for the answer purposes.

SQL script

/*
 * DBeaver Version 23.2.0.202309041200
 * `db2:11.5.8` See. https://hub.docker.com/r/ibmcom/db2/tags.
 * 
 * DDL use for the example.
 * 
 * The next three tables contain portion for the "Travel" entity.
 * 
 * CREATE TABLE "TEST-SCHEMA"."TRAVEL"  (
 * "TRAVELID" INTEGER NOT NULL, "PASSANGER_QUANTITY" INTEGER ) IN
   "USERSPACE1" ORGANIZE BY ROW;
 *
 * CREATE TABLE "TEST-SCHEMA"."TRAVEL_RATE" (
 * "TRAVELID" INTEGER NOT NULL, "RATE" DOUBLE ) IN "USERSPACE1" ORGANIZE
   BY ROW;
 *
 * CREATE TABLE "TEST-SCHEMA"."TRAVEL_TIME_TO_ARRIVE"  (
 * "TRAVELID" INTEGER NOT NULL, "TIME_TO_ARRIVE" INTEGER ) IN
   "USERSPACE1" ORGANIZE BY ROW;
 *
 * 
 * This table show a full "Travel" entity collecting data from the tables listed above.
 * 
 * CREATE TABLE "TEST-SCHEMA"."TRAVEL_RESULT"  (
          "TRAVELID" INTEGER , 
          "PASSANGER_QUANTITY" INTEGER , 
          "RATE" DOUBLE , 
          "TIME_TO_ARRIVE" INTEGER )   
         IN "USERSPACE1"  
         ORGANIZE BY ROW; 
 * */

-- This sintax use `WITH` and `INSERT` statements.

INSERT INTO "TEST-SCHEMA".TRAVEL_RESULT (
  TRAVELID, PASSANGER_QUANTITY, RATE, TIME_TO_ARRIVE
)
WITH 
collect_travel AS (
    SELECT 
        TRAVELID, PASSANGER_QUANTITY
    FROM "TEST-SCHEMA".TRAVEL
),
collect_rate AS (
    SELECT 
        TRAVELID, RATE
    FROM "TEST-SCHEMA".TRAVEL_RATE
),
collect_time_to_arrive AS (
    SELECT 
        TRAVELID, TIME_TO_ARRIVE
    FROM "TEST-SCHEMA".TRAVEL_TIME_TO_ARRIVE 
)
SELECT 
    collect_travel.TRAVELID,
    collect_travel.PASSANGER_QUANTITY,
    collect_rate.RATE,
    collect_time_to_arrive.TIME_TO_ARRIVE
  
FROM 
    collect_travel
    LEFT JOIN collect_rate ON collect_travel.TRAVELID = collect_rate.TRAVELID
    LEFT JOIN collect_time_to_arrive ON collect_travel.TRAVELID = collect_time_to_arrive.TRAVELID
;

Result set

Result set

I hope it helps you!

Upvotes: 0

CoOl
CoOl

Reputation: 2797

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.

Upvotes: 0

John Woo
John Woo

Reputation: 263933

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

Upvotes: 2

Taryn
Taryn

Reputation: 247870

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

Upvotes: 91

Related Questions