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
8
votes
2answers
12k views

Using INSERT INTO...EXEC AT linked server into temp table fails with Msg 7391

I am using SQL Server 2014. I want to execute EXEC (@remotecmd) AT [server_name]; (@remotecmd is a dynamic sql not a stored procedure) into ##eapb. My code is insert into ##eapb EXEC (@remotecmd) AT [...
0
votes
1answer
89 views

Temp table lives after being explicitly dropped [duplicate]

I'm creating a post-deployment script in VS2017 database project. In this script, I'm using single temp table, which is re-created frequently to adjust to different data structures. Below you can see ...
13
votes
1answer
945 views

SQL Server table name starting with # in user database, not in tempdb, not a temp table

Somehow, decades ago, a table got created in our database that starts with a #. It shows up in Object Explorer under the app's database, not in tempdb. For some reason, Azure won't import the database ...
6
votes
2answers
12k views

Postgresql: when temporary tables are deleted in postgresql

I am inserting data into temp table and its working fine. below is the psedo sql code for the same with cte as( ) select * into temp_table from cte with this approach data is inserted very fast ...
0
votes
1answer
33 views

can my SQL Server application code using temporary tables be used for Postgres?

I have a large application that connects to SQL Server and makes extensive use of temporary tables using the #table syntax. I would like to convert this to connect to postgres, which does not accept ...
9
votes
1answer
3k views

Bad execution plan after stats update due to temp table

A stored procedure query sometimes gets a bad plan after stats update on one of the tables, but can be recompiled to the good plan right after. Same compiled parameters. Issue seems to come from a ...
12
votes
1answer
7k views

Why does truncating a temp table at the end of the stored procedure that creates it free tempdb space faster?

SQL Server caches temp tables created within stored procedures and merely renames them when the procedure ends and is subsequently executed. My question has to do with when the tempdb space is ...
6
votes
2answers
1k views

Can SQL Server Always Encrypted be used for temporary tables?

I'm trying to determine if I can use Always Encrypted on a column in a temporary table (SQL Server 2016 SP1 Enterprise on Windows 2016). I've tried as follows: CREATE TABLE #listofdates (compare_dt ...
1
vote
1answer
83 views

Optimize series of queries that recycle the same filter

I have about 30 queries across 50 tables in total which each pull or aggregate information to be later all joined together. The original code provided used entirely temp tables, but it is difficult to ...
0
votes
1answer
792 views

Postgresql using LEFT JOIN and RIGHT JOIN at the same time

I have two temporary tables that I have created using tampa, tampb clauses in PostgreSQL. I want to be able to get result when data from either side is missing (company1 or company2) but not both. I ...
3
votes
1answer
5k views

Make a temporary table visible for a function inside a function (or a subfunction)

I have two functions in PL/pgSQL: The function 1 calls function 2, and function 2 needs to process a temporary table created on function 1, in another words, this temporary table needs to be global ...
2
votes
3answers
2k views

Can one have Temporary Rows?

I understand that Temporary Tables in Postgres and MS SQL Server persist until the end of the session. When the user’s session ends, any temp tables created by that user are dropped automatically. The ...
-4
votes
1answer
2k views

Split hyphenated nvarchar value [duplicate]

I have a table with 6 columns one of which is a NVARCHAR (65) datatype. All of the entries in this table have the format XXXXX-nnnnnn. I want to be able to use the '-' as a delimiter and construct a ...
9
votes
1answer
14k views

SQL server temp table using # - is it only accessible by the containing query for multiple connections with the same login?

Say we have 2 completely different queries, that reference a temp table with the same name: query one ....operates on: #tempTableName query two ....operates on: #tempTableName I've ...
3
votes
2answers
444 views

Create an explain plan for query over a global temporary table

I have a query which aggregates data from a global temporary table. The table will be filled right before the aggregation. My goal is a reliable explain plan for that query. Is there a way in ...
5
votes
2answers
354 views

Temp Table Clustered Key Not Being Honored: Bug or Expected Functionality?

As I was putting some test sets of data together, I noticed some funny behavior with temp tables. When working with large sets of data in clustered temp tables that are populated via a parallel ...
1
vote
1answer
853 views

SQL query output to Sybase temp table

I need to query a MSSQL 2008 db for 4 columns of data where one column (an ID) is used to join a number of tables from a Sybase ASE 15 database then output all 4 columns from the SQL query with a ...
1
vote
2answers
41 views

I need to use a permenent table instead of temporary but I still need the table's data to be private to the connection

I currently have an issue whereby an application which I maintain uses temporary tables. This is an issue as it's causing replication errors like found in this mariadb issue. I'm trying to think of ...
1
vote
1answer
3k views

drop table in a begin try begin catch block - is there any loss of performance?

The code below is a dynamic sql that will give you a list of all alerts in your system. It works. There is a temp table in the code for reasons beyond this question. This question is related to the ...
0
votes
0answers
455 views

Speed up creation of temp-table from select

I've got a query that runs in about 1.6 seconds and returns 150Kish rows. I have been building a memory engine based temp table from this query and this operation takes about 6.5 seconds. I've sifted ...
9
votes
2answers
2k views

When exactly are multiple users unable to simultaneously run a stored procedure with a temp table?

I have a question regarding a piece of documentation on Temp Tables that I recently read on TechNet. The fourth paragraph of the Temporary Tables section on that page reads as follows: If a ...
6
votes
1answer
4k views

Statistics on temp tables

Quick and simple... why is this sp failing when I attempt to retrieve statistics for one of the columns on the temp table? CREATE PROCEDURE dbo.Demo AS BEGIN SET NOCOUNT ON -- Declare table variable ...
2
votes
3answers
6k views

Combining multiple query result sets that share common columns into one specified temp table

Currently I am trying to combine three views into one stored procedure for a report. At first I started off using joins for the common data fields but found one of the tables, XFER, had data that was ...
2
votes
1answer
1k views

Oracle: changing a global temporary table to a normal table and vice versa

Our code make use of a global temporary table which the option 'ON COMMIT DELETE ROWS'. Works well and does the job as expected. But when running my automated tests, i would like the rows to persist,...
1
vote
2answers
126 views

How do I rewrite my query to use less temporary space?

I'm using Postgres 9.5. I want to identify (and then delete) rows from my table that are "duplicates" in the sense that they contain three fields that are the same, namely my_object_id name ...
1
vote
1answer
9k views

In Postgres, how do I adjust the "pgsql_tmp" setting?

I"m using Postgres 9.5.4 on Ubuntu 14.04. I have my Postgres data on a separate disk partiion, which is getting pretty full. myuser@myproject:~$ df -h /mnt/volume-nyc1-01/ Filesystem Size Used Avail ...
3
votes
2answers
14k views

Updating a table from a temporary table where clause error in MySql Community

I am trying to update an existing table named "residential_summary" from a temporary table called "a". I can query the temporary table a but when I try to use it in an update statement I get Error ...
3
votes
2answers
4k views

Using table variable instead of temp table makes query execution slow

I have a table with historical data about cars AutoData with combined clustered key Cas (DateTime) + GCom (Car ID). One record contains various indicators, like fuel level, vehicle state etc. ...
6
votes
1answer
1k views

Temporary Tables With Nonclustered Indexes Including All Columns

Background: Once per day several temporary tables that calculate a variety of different aggregated values are created. They all contain the same unique identifier (the field I would make a PRIMARY KEY ...
3
votes
1answer
6k views

Names of temp tables

I created a temp table which resides in the temp table folder. The problem is the name is unacceptably long which make it almost impossible to efficiently work on querying some field from it. Is it ...
0
votes
1answer
2k views

how to use with clause results as a temporary table

+----+-----------+--------------+-----------+ | id | file_name | file_value | file_type | +----+-----------+--------------+-----------+ | 1 | file1 | 8,624 | 1 | | 2 | file2 ...
0
votes
1answer
45k views

How to loop through tables dynamically and INSERT INTO a temporary table in MSSQL

I have an MSSQL (2016) DB with multiple tables for accounts of customer that have similar names like bandwidth_timeseries_ACC_1111111, bandwidth_timeseries_ACC_222222 etc. They are identical to each ...
3
votes
1answer
3k views

Values of created_tmp_disk_tables increases

I noticed that the values of Created_tmp_disk_tables increased. At a certain time, MYD files are created in /tmp directory and my site run very slowly. I am forced to restart the mysql service to ...
2
votes
1answer
774 views

Indexing a temp table

Would it be a good idea to apply indexing on temp tables when it is suggested to do so in the estimated execution plan?
1
vote
0answers
11 views

Memory usage when refining temporary table into other smaller temporary table

I'm working on a algorithm to explore different connections between two data sets and I am wondering how smart InnoDB is with memory usage in regards to temporary tables. I have a tree of conditions ...
0
votes
1answer
625 views

Mysql/InnoDB says it doesn't have access rights to create temp tables, but it does

For mysql Ver 14.14 Distrib 5.6.33, for Linux (x86_64) using EditLine wrapper in my.cnf: tmpdir = /var/mysql_tmpfs in /etc/apparmor.d/usr.sbin.mysqld: /var/mysql_tmpfs/ rw, /var/...
-2
votes
1answer
289 views

Merge multiple rows in variable type varchar

I Have temp table name @Definition which have 2 columns one for id and Field value I Have to take all rows in the field value as string in @P_Output declare @P_Output varchar(500); set @P_Output=(...
4
votes
2answers
3k views

Create global temp table on one DB and use it in a query on another DB - is it possible?

My issue is as follows: I'm trying to create a global temp table using data from DB1, then use that temp table in a query executed in DB2 and then have the results imported into a new table on DB2. ...
0
votes
2answers
1k views

MySQL ran out of disk creating an index, now I can't reclaim my disk

I've run a command on my MySQL database to add an index to a large table containing 8 million records (ironically so I could start deleting some of them): CREATE ...
0
votes
1answer
23 views

Temporary Table error when executing entirety of script

I'm having a problem with running the following script in it's entirety - That is, if I run it line-by-line separately it appears to work, but if I run everything at once, I get the error Message "...
9
votes
1answer
16k views

Index usage on a temporary table

I have two rather simple queries. The first query UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL; and it's plan duration: 0.125 ms plan: Query Text: UPDATE ...
7
votes
2answers
2k views

Indexes and Statistics on Temp tables

I've just upgraded our Data warehouse to SQL 2016. I'm seeing some really interesting graphs in the Query Store (I love this feature!). Below is the weirdest example i've seen. 22 plans for the same ...
1
vote
1answer
8k views

How to remove Using temporary?

Help me please, remove "Using temporary". I tried many different options, it is impossible to get rid of. Or sorting disappears or "Using temporary" appears. ;( Category table: `...
3
votes
1answer
2k views

Parallelism with temp table but not table variable?

The first query (inserts into table variable) takes twice as long as the second one. It does not use parallelism in the execution plan. The second query (inserts into temp table) uses parallelism in ...
3
votes
1answer
1k views

Incorrect key file for table error. How can I change location of MySQL's temp folder to another part of drive with more disk space?

I am using MySQL with Sequel Pro on a Mac Pro OSX Mavericks and am trying to run the code for a stored procedure in order to add two new columns to a table. The procedure does math operations on ...
1
vote
0answers
238 views

Inserting rows into a temporary variable causes plan cache pollution in SQL Server

In my database I use stored procedures to insert multiple rows from a temporary table variable. Inserting data into the temporary table variable causes a new adhoc plan to be created prior to each ...
0
votes
1answer
254 views

Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table

I have a query similar to this below. As you can see, there are multiple parts which are very similar. SELECT id, aaaa, bbbb, tags ,( SELECT COUNT(*) > 0 ...
4
votes
1answer
906 views

Temp postgres table ignores "excluding constraints"

In PostgreSQL 9.5. I'd like to create a temporary copy of my table without its primary key. Postgres has a feature { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ...
1
vote
1answer
62 views

MySQL Temporary Tables

I am facing Memory issue in our server. While debugging I came to know that, many number (millions) of temporary tables are created. Is there any way to identify which databases are creating this ...
6
votes
1answer
182 views

Benefit or use-case for empty variable / parameter / temporary table / temporary procedure names?

I just discovered, through sheer brilliance accidentally, that SQL Server allows you to create variables, parameters, table variables, temporary tables (local and global), and temporary stored ...