Hello All,

I am using the INTO clause of a select statement to store a resultset into a table into memory:

SELECT * INTO "\memory\testTable" FROM CUSTOMER_BOMS WHERE REF_DES = 'SOME TEXT'

works great.

Now I want to execute another query:

SELECT * INTO "\memory\testTable" FROM CUSTOMER_BOMS WHERE REF_DES = 'SOME OTHER TEXT'

and have this SELECT statement added to the record(s) generated by the first SELECT statement.

From reading the docs it would seem that this is not possible without first coding the creation of a table and then
using INSERT INTO statements to generate the contents of the table.

Any ideas would be appreciated.

Thanks

Oliver

Oliver,

The first SELECT creates the table, then you can add records to the same table
using INSERT:

SELECT *
  INTO "\memory\testTable"
  FROM CUSTOMER_BOMS
  WHERE REF_DES = 'SOME TEXT' ;

INSERT INTO "\memory\testTable"
  SELECT *
  FROM CUSTOMER_BOMS
  WHERE REF_DES = 'SOME OTHER TEXT' ;


--
Fernando Dias
[Team Elevate]

"Oliver" <dro@mettrix.com> wrote in message
news:EEEB8132-6423-4CCE-9EC2-5B100F766258@news.elevatesoft.com...
> Hello All,
>
> I am using the INTO clause of a select statement to store a resultset into
> a table into memory:
>
> SELECT * INTO "\memory\testTable" FROM CUSTOMER_BOMS WHERE REF_DES = 'SOME
> TEXT'
>
> works great.
>
> Now I want to execute another query:
>
> SELECT * INTO "\memory\testTable" FROM CUSTOMER_BOMS WHERE REF_DES = 'SOME
> OTHER TEXT'
>
> and have this SELECT statement added to the record(s) generated by the
> first SELECT statement.
>
> From reading the docs it would seem that this is not possible without
> first coding the creation of a table and then
> using INSERT INTO statements to generate the contents of the table.

Not true. The memory table has been creted with the first select into and is
now just like any other table. You can either insert the second select

insert into memory\testtable select * from customer_boms where...

or concatenate both selects

select * into memory\testtable from customer where ...
union all
select * from customer where ....

notice that on the second select you omit the into clause.

Robert

Thanks to all for the enlightment. I missed the fact that you can insert INTO on an INSERT statement. The only drawback is that
the first item requires a SELECT and the rest require INSERT which means different processing for the first item in the long list of
items that I have to process. Since the table is created on a SELECT statement if  the table doesn't exist I think it would be nice to have
an APPEND type directive available when using the SELECT INTO clause that allows for automatically creating the table if it does not exist
othewise appending the result of the SELECT if it does exist.

Thanks for all the info. I can work things out with the info provided.

Oliver

"Robert" wrote:


"Oliver" <dro@mettrix.com> wrote in message
news:EEEB8132-6423-4CCE-9EC2-5B100F766258@news.elevatesoft.com...
> Hello All,
>
> I am using the INTO clause of a select statement to store a resultset into
> a table into memory:
>
> SELECT * INTO "\memory\testTable" FROM CUSTOMER_BOMS WHERE REF_DES = 'SOME
> TEXT'
>
> works great.
>
> Now I want to execute another query:
>
> SELECT * INTO "\memory\testTable" FROM CUSTOMER_BOMS WHERE REF_DES = 'SOME
> OTHER TEXT'
>
> and have this SELECT statement added to the record(s) generated by the
> first SELECT statement.
>
> From reading the docs it would seem that this is not possible without
> first coding the creation of a table and then
> using INSERT INTO statements to generate the contents of the table.

Not true. The memory table has been creted with the first select into and is
now just like any other table. You can either insert the second select

insert into memory\testtable select * from customer_boms where...

or concatenate both selects

select * into memory\testtable from customer where ...
union all
select * from customer where ....

notice that on the second select you omit the into clause.

Robert