Using a global temporary table in this scenario could cause problems as the table would exist between sessions and may result in some problems using the calling code asynchronously. A local temporary table can be used if it defined before calling sp_executesql e.g. CREATE TABLE #tempTable(id int); execute sp_executesql N’INSERT INTO #tempTable SELECT myId FROM … Read more
You say you are new to Oracle. I’m guessing you are used to SQL Server, where it is quite common to use temporary tables. Oracle works differently so it is less common, because it is less necessary. Bear in mind that using a temporary table imposes the following overheads: read data to populate temporary table … Read more
No, a temp table is limited to the scope of your database connection. You can use the temp table in subsequent calls to the procedure during the same database connection, but other connections cannot access it. They can create a table by the same name, but each temp table will be independent. Temp tables go … Read more
select * from tempdb.sys.columns where object_id = object_id(‘tempdb..#mytemptable’);
CREATE PROCEDURE [MySPROC] AS BEGIN –supplying a data contract IF 1 = 2 BEGIN SELECT cast(null as bigint) as MyPrimaryKey, cast(null as int) as OtherColumn WHERE 1 = 2 END CREATE TABLE #tempSubset( [MyPrimaryKey] [bigint] NOT NULL, [OtherColumn] [int] NOT NULL) INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn) SELECT SomePrimaryKey, SomeColumn FROM SomeHugeTable WHERE LimitingCondition = true … Read more
You probably want CREATE TABLE AS – also works for TEMPORARY (TEMP) tables: CREATE TEMP TABLE temp1 AS SELECT dataid , register_type , timestamp_localtime , read_value_avg FROM rawdata.egauge WHERE register_type LIKE ‘%gen%’ ORDER BY dataid, timestamp_localtime; This creates a temporary table and copies data into it. A static snapshot of the data, mind you. It’s … Read more
I cannot reproduce the error. Perhaps I’m not understanding the problem. The following works fine for me in SQL Server 2005, with the extra “foo” column appearing in the second select result: IF OBJECT_ID(‘tempdb..#Results’) IS NOT NULL DROP TABLE #Results GO CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT ) GO select company, … Read more
Let’s answer the second question first: “why go away from the GTTs? are they really that bad.” A couple of days ago I was knocking up a proof of concept which loaded a largish XML file (~18MB) into an XMLType. Because I didn’t want to store the XMLType permanently I tried loading it into a … Read more
You first need to create your table first then it will be available in the dynamic SQL. This works: CREATE TABLE #temp3 (id INT) EXEC (‘insert #temp3 values(1)’) SELECT * FROM #temp3 This will not work: EXEC ( ‘create table #temp2 (id int) insert #temp2 values(1)’ ) SELECT * FROM #temp2 In other words: Create … Read more
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.