CODEWITHSUNDEEP
sqlcreate-tablehana
Thorsten Niehues
Thorsten Niehues

Reputation: 14472

SAP HANA create table / insert into new table from select

How to create a new table and insert content of another table?

Upvotes: 3

Views: 56928

Answers (3)

Wellington Gasparin
Wellington Gasparin

Reputation: 31

Like SQL Server you can create a temp table right from your select, the way is a little bit different.

Just execute:

temp_table = select 1 as col1, 'lorem ipsum' as col2 from dummy;

After that, you are able to use this temp table to query data from.

Like so:

select * from :temp_table;

Table Variable Type Definition

Unfortunately, there is some limitations using that. For example, you cannot simply insert new data. For that, exists some tricks.

Upvotes: 1

Lars Br.
Lars Br.

Reputation: 10396

Another, more SAP HANA specific solution is to use the 

CREATE TABLE ... LIKE <TABLE_NAME> WITH [NO] DATA ...

syntax (https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d58a5f75191014b2fe92141b7df228/content.htm#loio20d58a5f75191014b2fe92141b7df228__sql_create_table_1create_table_like_clause).

This allows more control over the physical properties of the new table.

Upvotes: 3

Thorsten Niehues
Thorsten Niehues

Reputation: 14472

create column table my_new_table as
(select * from my_existing_table)

Upvotes: 13

Related Questions