In SSMS I could create something like the temp table below. I could then run queries, joins, or additional temp table/cte creations off of #apps while only clicking the execute button ONCE.
SELECT
Visit_Type
,fullname
,appointment_date
,site_location
INTO #apps
FROM appointments
WHERE appointment_date BETWEEN '2024-07-01' AND '2024-07-31'
Now in a postgresql environment (dbeaver), I have to babysit each section of the code. Meaning; if the script contains 3 temp tables, I have to run them each independently. I've tried a variety of BEGIN and COMMIT calls but they seem to have no effect. Is there a way I can structure a script with temp tables and only have to execute the job once? Any help would be much appreciated.
For example, in dbeaver I would have to run these two sections separately. This is quite tedious for queries that have a long runtime. If I highlight both sections I get a 'syntax error at or near "CREATE"' error message. If I don't highlight, only the first section will execute.
---------------*Section 1*-----------------------
CREATE TEMP TABLE apps AS
SELECT
Visit_Type
,fullname
,appointment_date
,site_location
FROM appointments
WHERE appointment_date BETWEEN '2024-07-01' AND '2024-07-31'
;
---------------*Section 2*-----------------------
Select apps.*
,site_dep.department_name
from apps
left join site_dep
on apps.site_location = site_dep.location_name
and site_dep.state = 'CA'
;
In SSMS I could create something like the temp table below. I could then run queries, joins, or additional temp table/cte creations off of #apps while only clicking the execute button ONCE.
SELECT
Visit_Type
,fullname
,appointment_date
,site_location
INTO #apps
FROM appointments
WHERE appointment_date BETWEEN '2024-07-01' AND '2024-07-31'
Now in a postgresql environment (dbeaver), I have to babysit each section of the code. Meaning; if the script contains 3 temp tables, I have to run them each independently. I've tried a variety of BEGIN and COMMIT calls but they seem to have no effect. Is there a way I can structure a script with temp tables and only have to execute the job once? Any help would be much appreciated.
For example, in dbeaver I would have to run these two sections separately. This is quite tedious for queries that have a long runtime. If I highlight both sections I get a 'syntax error at or near "CREATE"' error message. If I don't highlight, only the first section will execute.
---------------*Section 1*-----------------------
CREATE TEMP TABLE apps AS
SELECT
Visit_Type
,fullname
,appointment_date
,site_location
FROM appointments
WHERE appointment_date BETWEEN '2024-07-01' AND '2024-07-31'
;
---------------*Section 2*-----------------------
Select apps.*
,site_dep.department_name
from apps
left join site_dep
on apps.site_location = site_dep.location_name
and site_dep.state = 'CA'
;
Share
Improve this question
edited Jan 30 at 0:56
Schwern
166k27 gold badges219 silver badges365 bronze badges
asked Jan 29 at 20:47
Adam HanlonAdam Hanlon
11 bronze badge
1
- Create a script and execute the complete script. See dbeaver/docs/dbeaver/Script-Management. On the other hand, this is one of the reasons I don't like DBeaver and don't use it anymore. – Frank Heikens Commented Jan 29 at 21:15
1 Answer
Reset to default 1DBeaver can run both sections of the above several ways:
- There is a vertical icon bar directly on the left of the SQL window. One of the icons runs the content of the window as a script.
- In the
SQL Editor
menu click the optionExecute SQL Script
. - In the SQL editor window use the shortcut key combination
ALT-X
. - You can highlight both sections and execute (CNTL-Enter) then both section will run. If you got a syntax error then something was wrong.
The above is the general case for executing multiple SQL statements.
In you specific case you can do this in a single statement. You either replace your temp table with a CTE or you just do a direct join of the tables.
-- convert temp table to CTE --
with apps as
( select visit_type
, fullname
, appointment_date
, site_location
from appointments
where appointment_date between '2024-07-01' and '2024-07-31'
)
select apps.*
, sd.department_name
from apps
left join site_dep sd
on apps.site_location = sd.location_name
and sd.state = 'CA';
-- direct Join of tables --
select apps.visit_type
, apps.fullname
, apps.appointment_date
, apps.site_location
, sd.department_name
from appointments apps
left join site_dep sd
on apps.site_location = sd.location_name
where apps.appointment_date between '2024-07-01' and '2024-07-31'
and sd.state = 'CA';