MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » November 2012

Re: change SQL select CTE to insert



fixed

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







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact