CTE insert

[kast1]

Is it possible to use a common table expression (CTE) with an insert? I want to select using "with" and then insert into another table using the results from the "with" expression. If you know can you give me a simple example, thanks.

[kpmac]

Re: CTE insert

I dont think so. I asked this question a few months back and I got no replies. Take a look at how I solved my problem.

http://code400.com/forum/showthread.php?t=2163

[kast1]

Re: CTE insert

Thanks kpmac I did not think it was possible (at least I could not find any examples).

[B.Hauser]

Re: CTE insert

Hi,

just for information CTE is a part of the SELECT statement and not any other SQL-Statement. A CTE can be used with an INSERT / UPDATE or DELETE only if a SELECT-statement is used along with this statements.

Here is a the anatomy of the select-statement:

Select-Statement:

   With .... (0 to n Commontable expressions)
   Full-Select (consists of 1 + n Sub-Selects combined with either UNION, EXCEPT or INTERSECT)
      Sub-Select ...
          SELECT ... (Field-Selection) including additional Sub-Selects
             FROM ... (Table-Selection) with all available JOIN-Clauses 
             WHERE ... (Conditions)
             GROUP BY ... (Grouping)
             HAVING ... (Conditions for Grouped Values)
      Union / Except / Intersect
      Sub-Select ...
Order By ... (Sorting) 

Birgitta

[kast1]

Re: CTE insert

Hi Birgitta

You say "A CTE can be used with an INSERT / UPDATE or DELETE only if a SELECT-statement is used along with this statements " so can I do something as simple as: with table1 as (select * from mytable), insert into table2 (select * from table1). Please give a simple example of an insert using CTE, thanks.

[BenThurley]

Re: CTE insert

Hi Birgitta

You say "A CTE can be used with an INSERT / UPDATE or DELETE only if a SELECT-statement is used along with this statements " so can I do something as simple as: with table1 as (select * from mytable), insert into table2 (select * from table1). Please give a simple example of an insert using CTE, thanks.

What would be wrong with INSERT INTO table2 (SELECT * FROM mytable)

Or if it was more complicated and needed a view perhaps...

INSERT INTO table2 (WITH table 1 AS (SELECT * FROM mytable) SELECT * FROM table1)

It's purely speculative but I would expect that to work.

[BenThurley]

Re: CTE insert

This was bugging me so I just had a play using STRSQL, below is the code that got it working.

INSERT INTO table2 WITH table1 AS (SELECT * FROM mytable) SELECT * FROM table1

[kpmac]

Re: CTE insert

Wow!
I never thought of trying it that way!

Learn something new everyday.

[BenThurley]

Re: CTE insert

Cool, pleased I could help

[kast1]

Re: CTE insert

Thanks, benthurley I have been trying to get this one for days.

[Mercury]

Re: CTE insert

As BenThurley showed INSERT using CTE INSERT above, can you please show me an example of DELETE statement using also CTE and the same syntax ?

[BenThurley]

Re: CTE insert

I don't believe you can reference a second table in a DELETE statement.

Those interested in relational database theory may know that Codd's 7th rule states that:

Rule 7: High-level insert, update, and delete:
The system must support set-at-a-time insert, update, and delete operators. This means that data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.

I read somewhere (sorry I can't remember where) that it has since been mathematically proven impossible to meet this rule for deletes. On a select you are getting data from a number of locations and you don't have to care where that data comes from. All you're interested in is the resulting set. In the insert example this was true also. It was basically just a select with an added step at the end of inserting the resulting set into a table.

To perform the delete the database would need to create the resulting set, work out which records to delete and then trace those records back to their original rows. The resulting set doesn't have this trace back and if it did it may only trace back to part of a row.

This technically means that a relational database (at least according to Codd) is not mathematically possible.

[Mercury]

Re: CTE insert

So much the worse if the delete can't work within a CTE. Thank you to spare your time.