CODEWITHSUNDEEP
sqlpostgresqltemp-tablesdbeaver
Adam Hanlon
Adam Hanlon

Reputation: 1

DBeaver temp table workflow

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'
;

Upvotes: 0

Views: 92

Answers (1)

Belayer
Belayer

Reputation: 14934

DBeaver can run both sections of the above several ways:

  1. 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.
  2. In the SQL Editor menu click the option Execute SQL Script.
  3. In the SQL editor window use the shortcut key combination ALT-X.
  4. 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';

Upvotes: 1

Related Questions