Subject Re: [firebird-support] Is it possible to INSERT records into an existing Query result?
Author unordained
---------- Original Message -----------
From: "Craig Cox" <data.inspector@...>
> I have a query that produces a set of records that then feed a report.
> The report is used by others to collect more information about known
> points (the subject of the report) in the field. Usually, the person
> using the report needs to enter new points by hand that are then
> entered into the database.
> Is it possible to write a query that finds specific records and that
> allows one to insert "blank" records as well?. Or, can an insert
> query be written that would allow a previous query result to act as a table?
> I am exploring this route because it would allow me to continue the
> report formatting more easily.
> Thanks in advance.
------- End of Original Message -------

Maybe I'm reading you wrong, but I think I'm understanding you differently than
Ann did. So just in case ...

In general, you can do this:
insert into report_temp (x, y ...)
select x, y ... -- your query

You can therefore take your initial report query, insert into a temp table for
editing, and let the user modify data there. The user could update, insert, or
delete in the temp table.

You can then bring data back from that temp table:
merge into main_table using (select x, y ... from report_temp) as changed
on =
when matched then update set x = changed.x, y = changed.y ...
when not matched then insert (x, y ...) values (changed.x, changed.y ...)

('merge into' does not support deletes; for that, you'll have to make your own
procedure, statements, etc.)

If want to provide the user direct access to modify data (in a grid, say) and
to do so you need a direct query (no temp table, no delay from the user
inputting data, then running a 'save' command that repopulates the original
tables, etc.) you can:

a) look at updateable result sets (I think those are supported in FB, for
simple queries? May depend on the libraries you use to connect to FB?) but I
don't know if those allow inserts, or if they only allow updates & deletes;

b) create a new VIEW each time a report is requested, and have the grid/report
come from a simple "select * from view_238423", then let your GUI component
make changes to that "table" (the client software doesn't know the difference._
If the view is too complex for FB to allow changes by default, you can provide
your own trigger on the view to perform the required changes (update, insert,
delete against any number of source tables.) For a dynamically-created view to
also have a dynamically-created trigger that works correctly no matter the
original query, that's probably a little crazy ... but it's an option. In
theory. You might even be able to use "with check option" to make sure any rows
the user adds would have shown up in the original query (a report of active
employees would not allow the user to add a new inactive employee through it.)