Like everyone else we use a lot of template tables in the dev environment.
I would like to know, the best practices with regards to these template tables in non-dev environment. Is it best to convert each and every template table to a permanent table, or identify the major ones and only convert those, or just uncheck the drop and recreate from the template table options.
Thanks.
brotherbob (BOB member since 2012-04-18)
Template tables should never be used in a non-development environment.
ww55d (BOB member since 2011-06-14)
Ditto.
eganjp 
(BOB member since 2007-09-12)
Ok Thanks.
But what about template tables that are used in data flows that have no direct impact on the target. There are tons of template tables that we use. I can understand why the final ouput tables need to be perm tables.
Isn’t unchecking the “Drop and Recreate Schema” adequate.
Sorry, to reiterate my questions but just want to really understand, why we need to import every single template table used in dev and then script it across all non-dev environments.
Thanks guys, not trying to be a pain in the ass.
brotherbob (BOB member since 2012-04-18)
Template tables are what I consider Zombie tables. The DBA doesn’t know about them so therefore they are unlikely to be optimized and they may show up where you don’t want them (in the default tablespace).
Say you turn off the “Drop and Recreate Table” option after the table already exists in your development environment. Now the ETL code moves to QA/UAT/Production. How does the table get created there? It won’t!!
At many client sites that I’ve worked with over the years the database login used by the ETL doesn’t have the CREATE TABLE permission. Without that your ETL will fail miserably when it tries to create the template table.
For me, the lack of a way to optimize the template table is the primary reason for not using them.
eganjp 
(BOB member since 2007-09-12)