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
1answer
17 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)...
9
votes
1answer
141 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
60 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
21 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
70 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
45 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
27 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
33 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
29 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
44 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
121 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
193 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
28 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
54 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
48 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
42 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
160 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
1k 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
73 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. ...
34
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
11 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
566 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
438 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
466 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
38 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
311 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
108 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
75 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
574 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
397 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
153 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
177 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
661 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
60 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
89 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
993 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
141 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
431 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
515 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
213 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
875 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 ...
12
votes
1answer
534 views

logical reads on global temp table, but not on session-level temp table

Consider the following simple MCVE: SET STATISTICS IO, TIME OFF; USE tempdb; IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL DROP TABLE #t1; CREATE TABLE #t1 ( r int NOT NULL ); IF OBJECT_ID(N'...
2
votes
1answer
556 views

Safe to use the same temporary table name in parallel transactons?

Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
-1
votes
1answer
290 views

executing SQL query with temp tables using 1.6jdk

I created a batch to run a query that has temp tables as the below example IF OBJECT_ID ('tempdb..#TEMP1') IS NOT NULL DROP TABLE #TEMP1 select * into #TEMP1 from cutomer IF OBJECT_ID ('tempdb..#...

1
2 3 4 5