Miguel Miguel - 1 year ago 178
SQL Question

Hana: How to create a table type LIKE the type of another table?

I'm trying to create a table type which has a lot of fields, in SQLScript for a Hana machine.

I've tried some combinations of 'Like' and other keywords but it all comes out as a syntax error.

Furthermore, I could not find any hint of this in the SQLScript reference guide.

I've been creating tables LIKE [orignal table] with no data and inserting records into it - not practical :(

Thanks in advance.

EDIT: to understand if the procedure 'get_object_definition' can be used with tables with case-sensitive names.

In this image we can see the procedure calls, with the error message; in the image after, the tables and table types in each of the schemas.
procedure calls


I don't know if I'm doing anything wrong here... Thanks again Lars.

Answer Source

There is no specific command to create a type based on an existing table or another type. What you can do is to get the definition of the table via

call get_object_definition ('<schema name>', '<table name>');

and edit the object creation statement to a CREATE TYPE statement. This is basically just changing the starting part of the statement and cutting away some parts at the end.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download