SQL create temporary table from query results: Can I?[SOLVED]
lazarus / 16-Databases

----------------------------------------------------------------------
OH1KH · 2023-07-06 18:09

Hi!
I have following question (linux, MariaDB & Freepascal):

I have done a query to use elsewhere in program and have results ready
in

...
Qinput.Open;
Qinput.Last;
Found:=Oinput.RecordCount;
Qinput.First;

Then I create similar temporary table as the table where Qinput
results are from to make another queries that are based to results of
Qinput contents

  trQtemp.StartTransaction;
  Qtemp.SQL.Text := 'create table temp_table like input_table';
  Qtemp.ExecSQL;
  trQtemp.Commit;    

Ok. Now I have empty copy of input_table.

Is there a way to insert query "Qinput" results directly to
"temp_table" contents (quick and fast)?

Or do I have to make a loop:

 While Qinput.Eof do
     begin
      //here make Qtemp.SQL.Text:= INSERT INTO temp_table (column1,
column2, column3, ...)
      // VALUES (Qinput.Fields[0].AsString,
Qinput.Fields[1].AsInteger, Qinput.Fields[2].AsString ...);
     Qinput.Next;
     end;

There are 55 columns and Qinput query may hold records from 1 to
100000 so making loop does not feel attractive.
I would prefer to write the Qinput contents somehow directly to
temp_table.
I think this copying could be done directly in SQL but as said the
Qinput query is already done elsewhere in FPC program where it is used
record by record, but now I want to make some other queries based on
Qinput results using temporary table and SQL.

I made Googling but did not find answer to this. Is this possible with
FPC without looping every record separately?

----------------------------------------------------------------------
TRon · 2023-07-06 18:16

My sql knowledge is next to none but as a suggestion could you perhaps
use INSERT INTO with SELECT ?

----------------------------------------------------------------------
paweld · 2023-07-06 18:44

Instead of copying to a temporary table, you can save in memory -
TMemDataset has a method that allows you to copy the structure and
data from another dataset: https://www.freepascal.org/docs-
html/current/fcl/memds/tmemdataset.copyfromdataset.html

----------------------------------------------------------------------
af0815 · 2023-07-07 03:49

The easiest way is to use a temporary table on the DB-server, see eg.
https://databasefaqs.com/mariadb-temporary-table/ . Here see "MariaDB
Temporary Table From Select" if you can create the table and insert
the data in one.

 If your intention is, to hold the data local (not on the server) you
have to use your statement. It can be written easier - you can look in
the resultset and build a statement on the fly with parameters and
prepare the query. After this the query will be faster (on the local
server).

This with a temporary table is used often in Stored Procedures to only
work with a slice of data.

----------------------------------------------------------------------
Zvoni · 2023-07-07 07:02

Quote from: OH1KH on July 06, 2023, 08:09:58 pm*snipp*
...
Qinput.Open;
Qinput.Last;
Found:=Oinput.RecordCount;
Qinput.First;If you want/need all records, set PacketRecords to 0 (or
was it -1?), then you don't need that Last/First thing.
Note: Don't remember anymore, but i think RecordCount is "unreliable"
(not sure!)
If you really just need the RecordCount beforehand, just fire off a
SELECT COUNT(*) FROM Wherever With whateverFilters beforehand.
Might even be faster


QuoteThen I create similar temporary table as the table where Qinput
results are from to make another queries that are based to results of
Qinput contents

  trQtemp.StartTransaction;
  Qtemp.SQL.Text := 'create table temp_table like input_table';
  Qtemp.ExecSQL;
  trQtemp.Commit;    

Ok. Now I have empty copy of input_table.

Is there a way to insert query "Qinput" results directly to
"temp_table" contents (quick and fast)?

There are 55 columns and Qinput query may hold records from 1 to
100000 so making loop does not feel attractive.
I would prefer to write the Qinput contents somehow directly to
temp_table.
As TRon wrote: INSERT INTO FROM SELECT

QuoteI think this copying could be done directly in SQL but as said
the Qinput query is already done elsewhere in FPC program where it is
used record by record, but now I want to make some other queries based
on Qinput results using temporary table and SQL. 
To me this sounds like you want to fire off secondary Queries based on
values from these records, you go through record by record (basically,
"downfiltering" your resultset).
Sounds to me that might be avoided if the original Query is set up
correctly (KeyWords: LEFT JOIN back to itself w/o CTE)

What's the goal of your Query, and how does it relate to your
secondary query?

----------------------------------------------------------------------
egsuh · 2023-07-07 07:34

Isn't MariaDB noSQL database? I'm not familiar with this sort of DB.

So, assuming SQL database...

Optimal method would depend on what you are going to do with the
second temporary query, and what to do on the first temporary query.
Possible methods would be:

1) Without second temp dataset, only working with the first query ---
using filter.
2) Copying the dataset to TBufDataSet, and working on the bufdataset.
3) Defining your first temporary query as "View" in database itself,
and using partial queries (e.g. your second temp query) from the view.

----------------------------------------------------------------------
Zvoni · 2023-07-07 09:31

Quote from: egsuh on July 07, 2023, 09:34:56 amIsn't MariaDB noSQL
database? I'm not familiar with this sort of DB.

MariaDB = Drop-In for MySQL

QuoteOptimal method would depend on what you are going to do with the
second temporary query, and what to do on the first temporary query.
Possible methods would be:

1) Without second temp dataset, only working with the first query ---
using filter.
2) Copying the dataset to TBufDataSet, and working on the bufdataset.
3) Defining your first temporary query as "View" in database itself,
and using partial queries (e.g. your second temp query) from the
view.As far as i understood OP, he wants to "downfilter" per record
from the first Query-Result.
Quote*snip*.. where Qinput results are from to make another queries
that are based to results of Qinput contentsThe only interesting thing
in this case is the expected RecordCount of that SubQuery:
0, 1 or more?
Because if it's 0 or 1 then he can just backjoin to the original table
via LEFT JOIN, and avoid using a second query

----------------------------------------------------------------------
OH1KH · 2023-07-07 10:49

Hi!

Thanks for comments!

This seems to be bad idea and does not have an easy way inside FPC.
There are too many "black holes" for me with FPC's SQL and datasets.
So I moved to SQL server side.

I changed thinking so that I made public string "inputSQL" from
Qinput.SQL.Text.
I.E the one that creates the predefined search query from database in
other part of program.

Using that string I    'CREATE VIEW my_view AS '+inputSQL  
After that I can make new queries against this "my_view".
Once all are done I   'DROP VIEW IF EXIST my_view' .

Seems to work fast enough, at least with small record counts in
created view.

Thanks for all!

----------------------------------------------------------------------
Zvoni · 2023-07-07 11:17

Quote from: OH1KH on July 07, 2023, 12:49:43 pmHi!

Thanks for comments!

This seems to be bad idea and does not have an easy way inside FPC.
There are too many "black holes" for me with FPC's SQL and datasets.
So I moved to SQL server side.

I changed thinking so that I made public string "inputSQL" from
Qinput.SQL.Text.
I.E the one that creates the predefined search query from database in
other part of program.

Using that string I    'CREATE VIEW my_view AS '+inputSQL  
After that I can make new queries against this "my_view".
Once all are done I   'DROP VIEW IF EXIST my_view' .

Seems to work fast enough, at least with small record counts in
created view.

Thanks for all!

And if you'd have just answered my question, all that might be
unneccessary.

What is the original Query, and how does the second query relate to
it?