SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




Install SAP Free


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.

SQL Select statement to create table on HANA database

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:

HANA database table columns created by SQL Select statement

This is a simple way of creating database tables dynamically on the fly for SQLScript developers on SAP HANA databases.






SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems