Questions tagged [temporary-tables]

The use of temporary table objects (not table variables) including semantics, design, performance optimization, and indexing. See also [table-variable].

Filter by
Sorted by
Tagged with
1
vote
3answers
56 views

Temporary table is full

The table '/mnt/disks/data/tmp/#sql58de_a0f6_7' is full The query is quite complex/big. Lots of joins in joins and unions, sorting, grouping, ... 1 out of 10 the result is returned. Otherwise it ...
1
vote
2answers
66 views

Should I use a temp table or join

I am creating a stored procedure in sql server 2019 that needs to use multiple select statements to get a parent row and then its related data. I have the primary key clustered value for the parent so ...
0
votes
0answers
15 views

Is Temporary table supported in Hive

I run queries in Hive...I wanted to create a temporary tables to save intermediate results of a complex query. Here is my query: CREATE TEMPORARY TABLE test AS SELECT * FROM my_table LIMIT 100 The ...
1
vote
1answer
19 views

Does MySQL support defining a table inside a session without appending it to the database?

As per stated in the title. Does MySQL support defining locally a table? (as a 'local variable' perhaps?) I understand (according to 'Source') that MySQL has 'two types of variables': (1) local and (2)...
10
votes
1answer
162 views

Why using a local temp table (instead of a global temp table or a regular table) influences the Query Optimizer to choose a poor query plan?

This Question brings a situation where the Query Optimizer chooses poorly the seek predicate among the existing predicates of a simple query. After running some tests I got to the conclusion that the ...
1
vote
0answers
143 views

MariaDB 10.3.27 - 100% Temporary tables created on disk despite/because of InnoDB?

I am using Mariadb 10.3 with InnoDB with a fairly high load. For some reason, I have 100% of my temporary tables created on disk, despite using Innodb and a large tmp_table_size (2G) (see Mysqltuner....
0
votes
2answers
31 views

How to avoid creating and dropping temp tables in functions?

I have been creating some functions lately that run into the "out of shared memory" warning in postgres. I have tried to increase the max_locks_per_transaction, but it still occurs. As far ...
0
votes
1answer
75 views

How to rewrite slow CTE construction to match speed of temp tables

I think the general advice of this community is to avoid temp tables in favor of CTEs. However, I sometimes encounter situations in which CTE constructions are very slow, while their temp table ...
1
vote
2answers
123 views

Unknown table 'table' in field list on referencing temporary table inside a trigger

I am creating a trigger BEFORE INSERT that checks a record from another table to see if it exists, otherwise signal sqlstate. The trigger definition shown below DELIMITER // DROP TRIGGER IF EXISTS ...
0
votes
1answer
46 views

Global Temp Table Error when Stored Proc run by SQL Job on a Schedule

I have a puzzling situation I am trying to confirm/solve. A stored procedure I have written creates a series of Global Temp Tables and fires off an email if any records show up in the final one. This ...
1
vote
1answer
35 views

See content of temporary Table in Oracles' EXPLAIN

I'm executing a Query in Oracle SQL Developer and eventually call SELECT * FROM TABLE ( dbms_xplan.display ); to get an impression what Oracle just did. There's a column Name in the output that ...
1
vote
1answer
26 views

Unable to save a stored procedure which contains a temporary table creation and update query

I am unable to save my procedure in Postgres. The procedure contains a temporary table creation and update query. Here is my query: CREATE OR REPLACE PROCEDURE account_report_v2() LANGUAGE SQL AS $$ ...
-2
votes
1answer
49 views

Same query performance problems when using ORDER BY [closed]

I have query that uses a few CTEs. The final output query joins the CTEs together. When I ran the query with no ORDER BY, it runs in seconds. When I ran it with an ORDER BY, it takes several minutes ...
0
votes
1answer
35 views

MariaDB: Why is this still using temporary and filesort - is it because I'm using a self-join? How to fix?

SELECT e2.personId, COUNT(*) FROM entries e1 JOIN entries e2 ON e2.categoryId = e1.categoryId AND e2.personId != e1.personId WHERE e1....
0
votes
1answer
57 views

HeidiSQL rounds down double values upon insert…select

I have the following table: CREATE TEMPORARY TABLE `new_aps` ( `MinuteStart` datetime NOT NULL, `ProxyListID` bigint(20) NOT NULL, `SuccessCount` double(22,0) NOT NULL, `UnknownCount` double(...
0
votes
1answer
238 views

Create table using select statement Truncated incorrect datetime value:

I have looked up different answers on similar question before posting it here. So i have a table with user id and date column I would like to store the results of select query to a temporary table. ...
0
votes
2answers
474 views

Select Into a temp table in DB2

Let me preface this question in that while I have a strong background in T-SQL but I am still just a newbie when it comes to DB2. The root query looks roughly like this: Select Col1, Col2, Col3 ...
0
votes
1answer
30 views

Querying an enormous table (~150 million rows) with multiple joins - SQL Server

First off, I don't have SHOWPLAN permission on this server or permission to add indexes, etc. I am not a database administrator by trade; I'm just trying to query a database. There is already indexes ...
1
vote
1answer
84 views

How do you minimize chance of collision between system-named constraints on temp tables?

We have occasionally hit the error “There is already an object named 'PK__#TempTab__0796211ACE71813B' in the database” when some of our procedures attempt to create temp tables with a system-named PK ...
0
votes
2answers
61 views

Dynamic auditting trigger without temporary tables

We currently have a trigger set up on a table to audit any data changes similar to this answer, which uses dynamic SQL to generate the audit rows. In order to use the INSERTED and DELETED tables in ...
0
votes
0answers
31 views

Why does UNION fail here?

I have a problem that I can't seem to figure out how to fix. What I want to do is simple, select data and append a row of sums as the last row. But I keep getting the error An error occurred in ...
0
votes
0answers
6 views

How to find join connecting flight legs from a dispatch table

Hello, suppose we have a flights table like shown on the left. Actually it contains much more data like departure time, aircraft etc but we dont need to work on them at the moment. the city "a" is the ...
2
votes
1answer
45 views

How to use Common Table Expression properly - Example where it seems to be very slow in TSQL

I've been starting using common table expressions more and more and thought I was using them "right" but I've run into an example where the solution with CTE is very slow compared to using a temp ...
-1
votes
1answer
250 views

Use a temp table's result in a view [closed]

I am using a temp table in a stored procedure. I need to use that temp table's result in my view. How can I do that?
2
votes
1answer
2k views

Sql Server string concat with order by and subquery

While working on some dynamic sql production code we encountered code that's concatenating a bunch of strings and a subquery and then more string concatenation. The goal of the code is to populate a ...
0
votes
2answers
82 views

Are key lookups from non-clustered indexes always slower than a second query that does the lookup?

I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries. ...
35
votes
2answers
4k views

Is it generally faster to select into a temp table than selecting into an actual table?

I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity? I thought I recall it saying something special about tempdb and ...
0
votes
0answers
12 views

Does the Query Optimizer have the right to create Temporary (Materialized) Views if it enhances performance?

Does the Query Optimizer have the right to create Temporary (Materialized) Views which can enhance performance? Sorry if this question seem very trivial. Or in other words, does the query optimizer ...
4
votes
1answer
752 views

Is there an easy way to SELECT FROM another session's temp table?

Is there an easy way to select the data from another session's temp table? I need to do so ad-hocly to debug an issue that I have limited visibility on otherwise. E.g. if I have access to TEMPDB, is ...
4
votes
1answer
508 views

If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?

If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp ...
0
votes
2answers
559 views

msdb.dbo.sp_send_dbmail temp table does not exist

I have created a temp table and would like to email the results I am getting the following error. Query execution failed: Msg 208, Level 16, State 1, Server PRODYCHDBX3\X3V7, Line 1 Invalid ...
1
vote
1answer
42 views

Unable to Insert Casted XML Data into New Column in Temporary Table

Question How can I insert CAST(event_data as XML) data into a newly added column in a temporary table with the following statement: INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(...
1
vote
1answer
398 views

Using WITH together with CREATE TEMPORARY TABLE AS SELECT … in Postgres?

I would like to create a temporary table using a select statement that uses temporary views specified in a WITH statement (not the WITH statement of CREATE TEMPORARY TABLE). e.g. something along the ...
1
vote
2answers
122 views

Replace function removing Ones / 1 when removing char(185) (superscript 1)

Ok as the title suggests this is occuring. When using the REPLACE function on char(185) for data in a temporary table, ones ("1") are being removed as well. SQL SERVER 2008R2 UNICODE Data DB ...
0
votes
2answers
105 views

How to split a table in SQL Server into two different related tables inserting foreign key?

I have a table OriginTable with the following structure EventID | Date | Value1 | Value2 | Value3 ----------------------------------------------------------- 5 | 2019-10-07 | ...
1
vote
2answers
716 views

SQL Server - global temporary table vs normal table

I have a stored procedure which I use to load csv files to SQL Server. This will initially bulk insert the file into a global temporary table then at the end it will do: select cast(column1 as int) [...
0
votes
1answer
518 views

How to solve a mail issue Failed to initialize sqlcmd library with error number -2147467259?

I got an error while trying to send an email: Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259. Here is my query: DECLARE @Table TABLE ( ...
1
vote
1answer
182 views

Postgres long running query not blocking table operations

We have a 3rd party service that imports data into a local postgres instance, that we then query/copy into our own view-data system. These queries can yield millions of rows, so the import of them to ...
0
votes
1answer
194 views

Aurora postgres grant permission to pg_attribute

In the process of migration from local postgres version to and RDS aurora postgres version. I'm using temporal tables and do for that reason have temporal tables that are inhirited from the origin. ...
0
votes
1answer
852 views

insert results from store procedure with parameters into table

I have a stored procedure that I want to insert into a table, the stored procedure however has parameters I have to pass, how can I do this? DECLARE @RC int DECLARE @startweek varchar(20) DECLARE @...
1
vote
0answers
65 views

How to avoid running multiple selects with the same subquery join?

I am using MySQL 5.7.26. I have 12 SELECT queries which run the same subquery join; since the subquery in a join is very complex (can be more complex, it is a filter query in a web app) I want to ...
0
votes
1answer
114 views

Oracle Global Temp table - data is truncated but the table remains in the DB ! is this an expected behavior or a configuration?

We are trying to create global temp table using Create global Temporary table t1 ( id NUMBER, description VARCHAR2(20) ) ON COMMIT Delete Rows; and trying to drop it at the end manually but ...
0
votes
1answer
29 views

Query to filter a table based on a comparison applied to the same table?

Case & Problem: Consider a corporate building with some rooms and departments that need authorization to enter. Employees have to scan their badges to be authorized. If the employee is inside (...
2
votes
2answers
1k views

MySQL - Can we use the same temporary table name in different stored procedure?

In our database, we generate statistics by periodically calling a large number of stored procedures in the event. Considering there are two SP auto_update_daily_users auto_update_daily_orders Is ...
0
votes
0answers
68 views

Allocating Rows From One Table to Available Date Range in Second Table

I designed a Shop Floor Scheduling System and it’s been running well for years, but the managers are now asking for something more precise and what they are asking for puts me outside my comfort zone. ...
0
votes
1answer
188 views

Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)

I am getting "ERROR: out of shared memory" presumably because my plpgsql function drops/creates a lot of temporary tables. Searching for a solution to the issue, I found this answer that says: ...
2
votes
1answer
497 views

Why can't temporary tables reference regular (non-temp) tables in PostgreSQL?

Why can't temporary tables references non-temp tables in PostgreSQL? When I run the following CREATE TABLE foo ( a int PRIMARY KEY ); CREATE TEMP TABLE bar ( a int REFERENCES foo ); I get, ERROR:...
2
votes
1answer
639 views

What types of locks are held on global temporary tables?

I am wondering if: the isolation level is Read Committed data is inserted or deleted or read are the same locks (shared for reading and exclusive for insert/delete) used for global temporary table ...
7
votes
1answer
216 views

Using `min_active_rowversion` for global temporary tables

I use global temporary tables for easy integration testing of my SQL Server backed application. But the function min_active_rowversion does not seem to include global temporary tables, like seen ...
0
votes
1answer
938 views

How to create and fill multiple session temp tables with dynamic SQL?

I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...

1
2 3 4 5 6