Create Table using SELECT Statement on SAP HANA Database
SAP HANA Database SQL developers can create tables dynamically using SQL Select statements and populate newly created table with output of SQLScript SELECT command. There are different methods for HANA SQLScript programmers to create database tables on SAP HANA database. One of the methods is executing SELECT statement and creating database tables with output of the query and using the selected field list and column properties of the source tables.
In this SAP HANA SQL tutorial, I will try to demonstrate database developers how they can create a new table on the fly by executing SQL Select command.
Assume that as SQLScript programmers on a SAP system, you want to store summary data from Sales Documents table VBAK and VBAP and store it into a new HANA database table.
I prepeared following SQL Select statement. You should replace the schema name with your system's schema name in the below SQL query.
select
A."KUNNR",
Count(Distinct A."VBELN") as OrderCount,
Sum(B."NETWR") as OrderNetValue,
Count(Distinct B."MATNR") as DifferentProducts
from "SAPABAP1"."VBAK" as A
inner join "SAPABAP1"."VBAP" as B
on A."VBELN" = B."VBELN"
group by
A."KUNNR"
Following output is produced on a SAP sandbox system.
What I want to do is instead of executing a seperate SQL command to run Create Table statement, I want to create database table on the fly.
Here is how database developers can create table on the fly using SQL Select statement.
create column table CustomerOrders as
(
select
A."KUNNR",
Count(Distinct A."VBELN") as OrderCount,
Sum(B."NETWR") as OrderNetValue,
Count(Distinct B."MATNR") as DifferentProducts
from "SAPABAP1"."VBAK" as A
inner join "SAPABAP1"."VBAP" as B
on A."VBELN" = B."VBELN"
group by
A."KUNNR"
);
Please note the HANA database SQL syntax requires SQL query surrounded by parenthesis. If you omit parenthesis you will get a syntax error during table creation.
Now in HANA database in your developer schema, you can see CustomerOrders table is created.
It is now possible to query the inserted data directly from summary table by executing;
select * from CustomerOrders;
And the table data columns are created automatically as follows:
This is a simple way of creating database tables dynamically on the fly for SQLScript developers on SAP HANA databases.
