All Forums Database
Richard34 9 posts Joined 02/14
18 Feb 2014
SSIS and Global Temp Tables

Hi. Can someone please tell me how Global Temp Tables work in Teradata?  Apparently, a connection to Teradata creates an instance of the table, and any data inserted into it is associated with that instance.  I am using Data Flow Tasks in SSIS 2008 to insert data into a temp table.  When I reference the temp table in a separate Data Flow Task, I get another instance of the table, which is empty.  I'm using 'On Commit Preserve Rows'.  

 

With Teradata SQL Assistant, I can insert data into the temp table, and retrieve it.  But, if I drop the connection and reconnect, the data is gone. Can someone please tell me how to insert data into the temp table, and use it before the data is dropped?  Thanks!

Adeel Chaudhry 773 posts Joined 04/08
18 Feb 2014

In your case, temporary tables .... be it Global TT or Volatile TT .... wont work.
 
Because, for GTT it retains its definition across all sessions, but data is only visible to session that inserted it.
 
VTTs on the other hand, gets dropped as soon as session is disconnected which created them.
 
You should use permanent table for your task for the data to be available across multiple sessions.
 
HTH!

-- If you are stuck at something .... consider it an opportunity to think anew.

M.Saeed Khurram 544 posts Joined 09/12
18 Feb 2014

Richard,
Global temporary table base definition is stored in data dictionary. When you create a GTT, an instance of this base definition is created against the session. Temporary table instance will be dropped at the session termination. So when you disconnect the session, temp table is dropped. 
If there is a compulsion to disconnect the session, then it would be difficult to use either Volatile or temp table. you can create a normal table and use it, and then drop it manually at the transaction end.
 

Khurram

Richard34 9 posts Joined 02/14
19 Feb 2014

Thanks Adeel for your advice, and thanks Saeed for your help too.  I was able to create a permanent table in EDW, and that solved it. You guys really rock! 

Adeel Chaudhry 773 posts Joined 04/08
19 Feb 2014

Welcome Richard! :)

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.