Archives of the TeradataForum

Message Posted: Mon, 17 Mar 2008 @ 10:39:34 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Error 5788: creating table from View structure

From:		McCall, Glenn David

In my task, the view will build dynamically. Depending on View I need to create table. Some times view may or may not have identity column. If view is not having any identity column I am able to create table without any issue. Now I am facing when view will have identity column.

I'm sure you mean when the base table behind the view has an identity column. Views don't have identity columns, only tables.

I haven't been really following this trail, so I don't know if this will help.

You can get the DDL of the table(s) behind the view using a "show select" query. You could then use the DDL to create a new table. With this model you could choose to preserve the identify column or not.

Here is one process that should be easy to implement in any programming language (e.g. C, Java, VB etc) or scripting tool (e.g. unix shells, perl, etc):

1) show select * from the_view;

2) Extract the DDL from the result sets returned. If the view is based upon just one table, the table DDL should be first. If the view could be based upon multiple tables, you will have a problem. In this case use the "prepared statement" process below.

3) Edit the DDL (using search and replace functions/regular expressions) to change the table name, database name and / or the identity definition.

4) Use the edited DDL to create your new table.

5) Insert select from your old table to the new one.

The other alternative, if you are using a programming language such as C, Java, VB etc is to:

1) "prepare" a select * from the_view

2) Use the ResultSet Meta Data to obtain the structure of the result set (columnnames, data types, column sizes etc)

3) use the result set meta data to generate a new table definition.

4) Insert select from your view to the new table.

With the "prepared" query example, I don't think you will get any information about the "identity" attributes of a column. I'm not sure what you are trying to achieve, but I'm guessing you want to create a backup copy of the data. In that case you probably don't need the identity column information.

Of course you only need to go through these complexities if you truly have to support identity columns in your source tables.

I hope this helps

Glenn Mc


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference