Getting around MySQL “Can’t reopen table” error
A simple solution is to duplicate the temporary table. Works well if the table is relatively small, which is often the case with temporary tables.
temp-tables
How can I simulate an array variable in MySQL?
Well, I’ve been using temporary tables instead of array variables. Not the greatest solution, but it works. Note that you don’t need to formally define their fields, just create them using a SELECT: DROP TEMPORARY TABLE IF EXISTS my_temp_table; CREATE TEMPORARY TABLE my_temp_table SELECT first_name FROM people WHERE last_name=”Smith”; (See also Create temporary table from … Read more
How do you create a temporary table in an Oracle database?
Yep, Oracle has temporary tables. Here is a link to an AskTom article describing them and here is the official oracle CREATE TABLE documentation. However, in Oracle, only the data in a temporary table is temporary. The table is a regular object visible to other sessions. It is a bad practice to frequently create and … Read more
Which are more performant, CTE or temporary tables?
It depends. First of all What is a Common Table Expression? A (non recursive) CTE is treated very similarly to other constructs that can also be used as inline table expressions in SQL Server. Derived tables, Views, and inline table valued functions. Note that whilst BOL says that a CTE “can be thought of as … Read more
Local and global temporary tables in SQL Server
Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends. Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed. Global temporary tables (CREATE TABLE ##t) are visible … Read more
SQL Server: Is it possible to insert into two tables at the same time?
In one statement: No. In one transaction: Yes BEGIN TRANSACTION DECLARE @DataID int; INSERT INTO DataTable (Column1 …) VALUES (….); SELECT @DataID = scope_identity(); INSERT INTO LinkTable VALUES (@ObjectID, @DataID); COMMIT The good news is that the above code is also guaranteed to be atomic, and can be sent to the server from a client … Read more
Inserting data into a temporary table
INSERT INTO #TempTable (ID, Date, Name) SELECT id, date, name FROM physical_table
When should I use a table variable vs temporary table in sql server?
Your question shows you have succumbed to some of the common misconceptions surrounding table variables and temporary tables. I have written quite an extensive answer on the DBA site looking at the differences between the two object types. This also addresses your question about disk vs memory (I didn’t see any significant difference in behaviour … Read more
What’s the difference between a temp table and table variable in SQL Server?
There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp), although using tempdb isn’t one of them, as spelt out in the MSDN link below. As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline … Read more
Create a temporary table in a SELECT statement without a separate CREATE TABLE
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1) From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use … Read more