Re: change SQL select CTE to insert

On 29 Nov 2012 11:31, Stone, Joel wrote:

I have a complex SELECT stmt that I would like to use to copy
(insert) records into another table.

I tried placing "INSERT INTO table-name" in front but that doesn't
work.

How can I change this SELECT to insert the result set into a
different file?

What is the effect for "doesn't work"? If the error is SQL0199 "Keyword WITH not expected. Valid tokens: ( SELECT.", then very possibly rather than simply prefacing the existing SELECT statement with the "INSERT INTO table-name" as implied, the SELECT had instead been enclosed in parentheses. So for example, given the following SELECT [using ellipses], the first attempted change to use that SELECT statement for an INSERT fails, but the second INSERT will pass muster and be able to run:

with
MISCCHGSRC as
( select ... )
select ...
;

insert into table_name
( with
MISCCHGSRC as
( select ... )
select ...
)
; -- -199 per parenthesis before WITH, CTE, and the fullselect

insert into table_name
with
MISCCHGSRC as
( select ... )
select ...
; -- the WITH is not diagnosed as improper; this runs