CODEWITHSUNDEEP
sql-serverteradata
cs0815
cs0815

Reputation: 17388

can one use temp tables in teradata

Is there an equivalent for this sql server tsql in teradata:

IF OBJECT_ID('tempdb..#SomeTempTable') IS NOT NULL DROP TABLE #SomeTempTable;

CREATE TABLE #SomeTempTable (Bla NVARCHAR(255));

INSERT INTO #SomeTempTable
SELECT N'a'
    UNION ALL
SELECT N'B'

Upvotes: 1

Views: 2040

Answers (1)

dnoeth
dnoeth

Reputation: 60462

There's no equivalent for the 1st statement, there might be a Stored Procedure for it like this:

REPLACE PROCEDURE Drop_Table_If_Exists
(
  IN db_name VARCHAR(128) CHARACTER SET UNICODE,
  IN tbl_name VARCHAR(128) CHARACTER SET UNICODE,
  OUT msg VARCHAR(400) CHARACTER SET UNICODE
) SQL SECURITY INVOKER
BEGIN
   DECLARE full_name VARCHAR(361)  CHARACTER SET UNICODE;

   DECLARE sql_stmt VARCHAR(500)  CHARACTER SET UNICODE;
   DECLARE exit HANDLER FOR SQLCODE 'T3807'--SQLEXCEPTION
   BEGIN
      IF SQLCODE = 3807 THEN SET msg = full_name || ' doesn''t exist.';
      ELSE
        RESIGNAL;
      END if;
   END;

   SET full_name = '"' || COALESCE(db_name,DATABASE) || '"."' || tbl_name || '"';

   SET sql_stmt = 'DROP TABLE ' || full_name || ';';
   EXECUTE IMMEDIATE sql_stmt;

   SET msg = full_name || ' dropped.';
END;

A VOLATILE table exists only within your current session (i.e. the same name might be used in different sessions for different tables) and is automatically dropped when the session disconnects. When you keep the naming convention (name of a temporary starts with #) you probably don't need the conditional Drop (you should know if you already created this table in the current session):

CREATE VOLATILE TABLE #SomeTempTable( 
Bla VARCHAR(255) CHARACTER SET UNICODE)
ON COMMIT PRESERVE ROWS;

Caution, if you don't specify the Primary Index it will default to a NUPI on the first column.

The Select has a strange restriction, you need a FROM when you do a Set Operation like UNION/INTERSECT/EXCEPT. Workaround is either a dummy view (similar to Oracle's DUAL table) like this:

replace view dummy as select 1 as x;

INSERT INTO #SomeTempTable
SELECT 'a' FROM dummy
    UNION ALL
SELECT 'B' FROM dummy

or a similar CTE:

INSERT INTO #SomeTempTable
WITH dummy AS (select 1 as x)
SELECT 'a' FROM dummy
    UNION ALL
SELECT 'B' FROM dummy

Upvotes: 1

Related Questions