Skip to main content

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
5 votes
1 answer
170 views

How does sp_executesql interact with temporary tables?

I'm at a loss as to why some SQL leveraging sp_executesql is working the way it is when temporary tables are involved, along with INSERT and SELECT - INTO. I'm using SQL Server. Here is the code in ...
Wipqozn's user avatar
  • 161
0 votes
0 answers
23 views

How do I feed the output arrow (`Input0_ProcessInputRow()`) of a second Script Component with data from a temp table of the first Script Component?

Here is the full setup and code that takes up this answer at DFT drops connection and its temporary table after leaving any DFT item, tested with two Script Components. How do I keep the temp table ...
questionto42's user avatar
0 votes
1 answer
128 views

DFT drops connection and its temporary table after leaving any DFT item, tested with two Script Components. How do I keep the temp table alive?

Links This takes up: Using temporary tables in SSIS flow fails - Stack Overflow Is it possible to use a temp table in a data flow source?; answer is: 'No', since it cannot be passed without being ...
questionto42's user avatar
0 votes
2 answers
347 views

How do I create and fill a temporary table with incoming data from a Data Source item without leaving the SSIS Data Flow Task C# Script Component?

I came to ask this question after coding in a script component for some time, testing mainly how to loop over columns of the Row object, see Looping Through Columns in SSIS Script Component - Stack ...
questionto42's user avatar
0 votes
0 answers
60 views

Do Primary Keys On Table Variables Have No Statistics At All? [duplicate]

My confusion is down to an apparent contradiction in principles. Primary keys are indexes and those always have statistics, but table variables notoriously always don't have statistics. What follows ...
J. Mini's user avatar
  • 525
0 votes
0 answers
93 views

How to insert data into temporary table from the table having encrypted columns in sql server

I am trying to insert data into a temporary table(#table1) from the table(table2) having encrypted column in it. I am getting below error while trying to insert data into temporary table from the ...
Prathap r's user avatar
0 votes
1 answer
248 views

Why use a memory-optimized table variable instead of a temp table or normal table variable?

Table variables have a bad reputation, mostly due to cardinality estimates. However, this reputation was earned before the introduction of memory-optimized table variables. Microsoft covers some use-...
J. Mini's user avatar
  • 525
0 votes
1 answer
117 views

Switching table partition into temporary table

I'm trying to switch a table partition into a temporary table with the following query: create partition function [PFCT_T](datetime2) as range right for values () create partition scheme [PSCH_T] as ...
Arokh's user avatar
  • 27
-1 votes
1 answer
78 views

Is it possible to create fast (ram only) temporary table for inter software communication purposes in MS SQL Server?

I was wondering that is it possible to create a table that is really fast and temporary (basically one that resides in ram) in MS SQL Server 2005 or later for inter software communication purposes? Do ...
The amateur programmer's user avatar
1 vote
1 answer
480 views

Is there any benefit to WITH RECOMPILE or OPTION (RECOMPILE) in a stored procedure that uses temp tables at every stage?

Suppose that I have a stored procedure. It does not call any other stored procedures and is not called by any others, nor is it called concurrently. Every step in this stored procedure either creates ...
J. Mini's user avatar
  • 525
1 vote
3 answers
422 views

MySQL Temporary Tables and Connection Pools

This is more of a "please confirm/correct my understanding" question. Background I connect to MySQL using JDBC, the connection pool has around 250 connections, most of which seem ...
DSZ's user avatar
  • 127
0 votes
1 answer
108 views

Why does inserting a value into a temp table's BINARY(8) column cause an error when casting the same value to BINARY(8) does not?

When I execute this: DROP TABLE IF EXISTS #QueryHashes; CREATE TABLE #QueryHashes (query_hash BINARY(8) NOT NULL PRIMARY KEY); PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(8)); -- just testing that the ...
Mark Freeman's user avatar
  • 2,191
1 vote
1 answer
402 views

Will Postgres reclaim space when a temporary table is dropped?

I'm using a large number of TEMP TABLES and want to make sure I'm not leaking storage. I create and use the table in a transaction with CREATE TEMP TABLE mytable ... ON COMMIT DROP. I'm having trouble ...
Joe's user avatar
  • 1,645
0 votes
0 answers
1k views

MySQL indexes on temp tables

I am trying to perform joins between two temporary tables, creating an index on the join key for each temp table. However, when I EXPLAIN the join, the row estimates always seem to come out vastly ...
im123's user avatar
  • 1
0 votes
1 answer
953 views

what queries in mariadb create .mad and .mai temporary files

During our nightly backup I saw that two files vanished while the backup was being performed: file has vanished: "/tmp/#sql-temptable-3ab-aca6d-316f6.MAD" file has vanished: "/tmp/#sql-...
proxymoxy's user avatar
0 votes
1 answer
67 views

Compose a complex query in Postgresql

I have a huge table like following: CREATE TABLE public.huge_table ( sampl_day date NOT NULL, tick_time timestamp(6) with time zone NOT NULL, crit_feat integer NOT NULL, --- --- ...
Leon's user avatar
  • 357
0 votes
0 answers
185 views

Temporary rows in static table in SQL Server

I'm looking for a way to insert temporary rows in a static table. These rows are temporary because are used only in processing. The reasons are: I wish not to work in transaction because it's a huge ...
Radioleao's user avatar
  • 127
2 votes
0 answers
147 views

MySQL release memory allocated for a temp table

I'm using 5.6 MySQL database to insert tens of millions records between multiple tables. Before the insertion I send all my data into a temp memory table, for that reason I have increased both ...
Maksim Vi.'s user avatar
0 votes
0 answers
166 views

Is a dbo.SORT table in a user database confined to only 1 data file?

A user thought he was exhausting tempdb because of this error: 'Could not allocate space for object 'dbo.SORT temporary run storage: 422291993526272' in database '<USER_DB>' because the '...
user avatar
-2 votes
1 answer
11k views

What is the correct way to check and delete temp table?

Approach 1: IF OBJECT_ID('tempdb..#MyTempTbl') IS NOT NULL DROP TABLE #MyTempTbl; Approach 2: IF EXISTS (SELECT * FROM [tempdb].[sys].[objects] WHERE [name] = N'#MyTempTbl') DROP TABLE [#...
variable's user avatar
  • 3,468
1 vote
0 answers
473 views

Azure SQL Error "Invalid object name '##{myTempTableName}'

I am getting an error in my stored procedure: "Invalid object name '##{myTempTableName}' for 3 global temporary tables. There are several temp tables used in the procedure, but I am getting this ...
One More Tea's user avatar
0 votes
1 answer
163 views

How does a serializable transaction level affect global temporary tables in SQL Server? Creating table affects it?

I have a knowledge gap in SQL Server 2019 and if someone could explain it to me how the following works appreciate it. This will be a long post as I want to explain the full context as best I can ...
Brian Karabinchak's user avatar
0 votes
1 answer
204 views

Solutions for fixing stored procedures that rely heavily on global temporary tables?

I am working on re-writing some stored procedures. The last issue was an abuse of EXECUTE AS USER='someUser' to use the default schema and work against those. The second thing I just noticed though ...
Brian Karabinchak's user avatar
1 vote
1 answer
1k views

How does PostgreSQL resolve pg_temp?

pg_temp is an alias for current session's temporary-table schema. Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in ...
dpprdan's user avatar
  • 111
3 votes
1 answer
526 views

Invalid Object Name after Creating a Table using a while loop

I created this loop to create and populate a temporary table for each int representing a day. The tables are created and properly executed but i get Invalid Object Name everytime i try to print a ...
ADAMS zequi's user avatar
2 votes
2 answers
1k views

CTE vs. temp table for batch deletes

Two-part question here. We have a large table (between 1-2 million rows) with very frequent DML operations on it. During low volume periods, we have an agent job to remove older rows to keep the ...
DBA Greg14's user avatar
3 votes
1 answer
217 views

Valid for WITH clause to use existing table name?

Suppose I have a table Entry. Is it valid for a WITH clause to replace the Entry table for the duration of a query? I tried with SQLite and MySql and it seems to work, but I want to be sure this is ...
ggambetta's user avatar
  • 133
-1 votes
1 answer
168 views

When DML and DDL changes occur on a Temporal Table do the correlating changes in the History table get logged in the Transaction Log as well?

When DML and DDL queries are executed against a Temporal Table, those changes are appropriately propagated to the correlating History table. E.g. an UPDATE to the Temporal Table results in a new ...
J.D.'s user avatar
  • 38.3k
0 votes
1 answer
498 views

Teradata: Temporary Table vs Create and Drop Table

Currently I have seen an ETL process where a user created tables that are used to store an intermediate result and then drop the tables at the end of the ETL process. At first glance it seemed to me ...
Enrique Benito Casado's user avatar
3 votes
2 answers
2k views

UPDATE FROM with a large table is slow and uses Seq Scans

I have a large table (ultimately maybe a billion rows but currently ~26 million) for which I want to set a flag on the highest PK for a given grouping, in a one-off batch. I chose to create a ...
Joe's user avatar
  • 1,645
0 votes
1 answer
63 views

When Insert Press Name Tech Id Take Wrong Number Where Press Name Exist?

I work on SQL server 2017 I have table #Pressfeature as below create table #Pressfeature ( PartId int, PressName varchar(300), TechId int ) insert into #Pressfeature(PartId,PressName,...
ahmed barbary's user avatar
2 votes
1 answer
186 views

Alter the collation of a column in temp-file

I have a routine in a DB that uses a temp-file. But the collation of the DB doesn't match the collation of the temp-DB. I can't change the collation of the DB, it's a client DB. I tried this: BEGIN ...
Florian's user avatar
  • 31
3 votes
2 answers
702 views

How can Temp Table creation time be reduced in terms of CPU time?

Inside a frequently executed stored procedure (sometimes up to 10-15 times per second), there are two particular statements that show up in Top 10 heaviest queries by cumulative CPU impact These are ...
Aleksey Vitsko's user avatar
0 votes
1 answer
3k views

How to create multiple temp tables using records from a CTE that I need to call multiple times in Postgres plpgsql Procedure?

I am already using a CTE expression within a plpgsql Procedure to grab some Foreign Keys from (1) specific table, we can call it master_table. I created a brand new table, we can call this table ...
XxAemaethxX's user avatar
1 vote
1 answer
2k views

Automatically materialize CTE? (instead of temporary table CREATE and INSERT)

I’m a novice trying to learn about query optimization and temporary tables in Oracle. The examples I’ve seen for Oracle temporary tables involve CREATE TABLE and INSERT INTO statements. CREATE ...
User1974's user avatar
  • 1,477
3 votes
3 answers
2k views

Reducing Temp Table Scans when joining two temp tables using OR conditions

I'm working on a complicated query. I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
scarr030's user avatar
  • 115
9 votes
2 answers
814 views

How does the optimizer treat yet-unpopulated #temp tables when estimating cardinality?

I've been building stored procedures and can't find any documentation on how the SQL Server optimizer handles #temp tables. If I have 4 joins to a #temp table where that #temp table is created DURING ...
Mark Adam Proctor's user avatar
2 votes
1 answer
5k views

There is already an object named PK_MyPrimaryKey in the database ( primary index on temp table)

Simplified code var sql = @" create table #Temp( int Id NOT NULL CONSTRAINT [PK_TempId] PRIMARY KEY (Id) ) insert into #temp(id) select (...
Kirsten's user avatar
  • 185
0 votes
2 answers
2k views

ORA-01652 - unable to extend TEMP segment?

Having an issue that I can't seem to find any solutions to online. [72000][1652] ORA-01652: unable to extend temp segment by 256 in tablespace TEMP Position: 581 One of our users runs a query ...
Phillip's user avatar
  • 11
0 votes
1 answer
68 views

Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server

HI I have a server with 64 GB Ram and I have assigned around 52GB to the SQl Server itself. However Even this has effected the performance of the server and the web application hosted in the same ...
srikanth reddy varkala's user avatar
1 vote
1 answer
975 views

MySQL indexing on 11 million rows

I have a development and production server, both with different amounts of RAM. Development machine has 8G, while production has 64G. I'm importing a large table from development to production, but ...
Mark's user avatar
  • 111
0 votes
1 answer
3k views

Create an Azure Synapse temp table from a SELECT with ORDER BY

I have the following statement that works when I omit the ORDER BY clause, or if I run it as a select and omit the create table part, but I need both to ensure my generated key is sequenced correctly ...
Lauren_G's user avatar
3 votes
5 answers
10k 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 ...
pierot's user avatar
  • 143
2 votes
3 answers
4k 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 ...
Joshua Grippo's user avatar
1 vote
1 answer
25 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)...
Gaston's user avatar
  • 113
10 votes
1 answer
504 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 ...
Ronaldo's user avatar
  • 5,636
1 vote
0 answers
2k 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....
P S's user avatar
  • 11
0 votes
2 answers
3k 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 ...
Jonas Olsen's user avatar
0 votes
1 answer
4k 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 ...
SlowMagic's user avatar
  • 151
1 vote
2 answers
1k 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 ...
Karuntos's user avatar

1
2 3 4 5 6