temp-tables

EF4 – The selected stored procedure returns no columns

EF doesn’t support importing stored procedures which build result set from: Dynamic queries Temporary tables The reason is that to import the procedure EF must execute it. Such operation can be dangerous because it can trigger some changes in the database. Because of that EF uses special SQL command before it executes the stored procedure: … Read more

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

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

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

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

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