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
Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow

Create Table Dynamically from Existing on SAP HANA Database using SQLScript


SAP HANA SQLScript doesn't let SQL programmers to create temporary tables based on an existing database table using a simple SQL Select statement as in Transact-SQL for example. So when I experienced such a case, I tried to create a dynamically build SQLScript "CREATE LOCAL TEMPORARY TABLE" statement which reads the structure of the target database table and modifies the dynamic SQL string according to column names and data types of the source table.

Unfortunately, this local temporary table cannot be directly accessed or explicitely within SQLScript anonymous block or in an AMDP SQL codes. So I had to convert all codes processing data using temporary table into dynamic SQL statements as well.

In this tutorial, I'll show how you can create a temporary local table dynamically and insert data into temporary table. Finally I'll also share codes to drop temporary table for SQLScript developers.


Read All Columns of a Table on SAP HANA Database using SQL

In my sample case for this tutorial, I want to create a local temporary table and store data from KNA1 table for example.

First of all, I can read the source database table column names and data type and length properties of each column of the table from sys.table_columns system view.
Here is the SQLScript code to read all columns of a table with column properties from SYS.TABLE_COLUMNS view:

select
 column_name, data_type_name, length, scale, position
from sys.table_columns
where table_name = 'KNA1'
order by position
Code

read column list of a table on SAP HANA database


Build Create Temporary Table Command Dynamically using SQLScript

Of course, it is important to build a single SQL command that will create a table using above column properties.

If SQL programmers pay attention to following code block, they will realize I concatenated column name and its data type properties using string concatenation expression "||" for each column of the database table within CTE expression.

And in the outer SELECT statement, I used SAP HANA SQLScript string concatenation or string aggregation function STRING_AGG in order to build a unique meaningful CREATE TABLE statement.

Please note that for certain HANA database versions, ORDER BY clause for string_agg function fails to execute successfully. Please check your HANA database version, if you experience such an error.

with columns as (
 select
  position,
  '"' || column_name || '" '
  || data_type_name || '('
  || length
  || (case when scale is null then '' else ',' || scale end)
  || ')'
  as columnStr
 from sys.table_columns
 where table_name = 'KNA1'
 order by position
)
select
 'create local temporary table #tempTABLENAME ( ' ||
 String_Agg(columnStr, ', ' order by position) ||
 ');'
 as SQLCommand
from columns;
Code

Here is the output displayed when I execute above SQLScript SELECT command on SAP HANA Studio on a S/4HANA database

create local temporary table on SAP HANA database using SQLScript


Store Selected Data in SQL Variable

To store the resulting concatenated string which forms a SQL command to create database object, following syntax can be used.
Please note that, variable name should be in capital letters within ' "" ' following INTO clause before FROM clause.

declare sqlVariable nvarchar(100);
select NEWUID() into "SQLVARIABLE" from dummy;
select :sqlVariable from dummy;
Code

Here is how database programmer can use this solution for storing CREATE TABLE script into SQL variable.

declare SQLQUERY varchar(8000);

...

with columns as (
 select
  position,
  '"' || column_name ||
  '" ' || data_type_name ||
  '(' || length ||
  (case when scale is null then '' else ',' || scale end) || ')' as columnStr
 from sys.table_columns
 where table_name = :SOURCETABLE
 order by position
)
select
 'create local temporary table ' || TABLENAME ||
 ' ( ' || String_Agg(columnStr, ', ' order by position) || ');'
into "SQLQUERY"
from columns;
Code

Execute Dynamically Build SQL Statement using EXECUTE IMMEDIATE

If I can store concatenated SQLScript command into a SQL variable (for example SQLQUERY), I can evaluate it or execute the command by using EXECUTE IMMEDIATE statement as follows

EXECUTE IMMEDIATE (:SQLQUERY);
-- or
-- EXECUTE IMMEDIATE :SQLQUERY;
Code

Unfortunately, although you can create the local temporary table by using above methods dynamically, it is not possible to access it or use it in a SQLScript code explicitely.
To make the situation more clear, following code will cause SQL exception if exxecuted after table is created dynamically by EXECUTE IMMEDIATE command

SELECT * FROM tempTABLENAME;
Code

Here is the error you will get if you try to query temporary table right after its creation in the same session:
SAP DBTech JDBC: [259]: invalid table name: Could not find table/view {tablename} in schema {schemaname}

It is interesting that SQL developers can query the new database table if they open a new console and execute a SELECT statement on it.

In the same code block, to query new local temporary table, HANA database developers have to continue our SQL code development using dynamic statements.

select 'SELECT * FROM ' || TABLENAME into "SQLQUERY" from dummy;
EXECUTE IMMEDIATE :SQLQUERY;
Code

SQLScript Code to Create Local Temporary Table on SAP HANA Database

Up to now in this SQLScript tutorial, I shared the steps and SQL code fragments with ABAP programmers to create local temporary table dynamically from an existing SAP HANA database table like KNA1.
Now SQL developers can combine all SQLScript code blocks to build a single SQL script which will create a temporary table, name it dynamically, populate temporary table with data, query temporary table and finally drop table at the end of the SQL code block.

do
begin

declare SQLQUERY varchar(8000);
declare TABLENAME nvarchar(256);
declare SOURCETABLE nvarchar(256);
SOURCETABLE = 'KNA1';
TABLENAME = '#tempTABLENAME';

with columns as (
 select
  column_name, data_type_name, length, scale, position,
  '"' || column_name || '" ' || data_type_name ||
  '(' || length || (case when scale is null then '' else ',' || scale end) || ')'
  as columnStr
 from sys.table_columns
 where table_name = :SOURCETABLE
 order by position
)
select
 'create local temporary table ' || TABLENAME ||
 ' ( ' || String_Agg(columnStr, ', ' order by position) || ');'
 into "SQLQUERY"
from columns;

EXECUTE IMMEDIATE :SQLQUERY;

select 'INSERT INTO ' || TABLENAME ||
 ' (KUNNR) SELECT TOP 10 KUNNR FROM "SAPS4S".' || SOURCETABLE || ';'
into "SQLQUERY" from dummy;
EXECUTE IMMEDIATE :SQLQUERY;

select 'SELECT * FROM ' || TABLENAME into "SQLQUERY" from dummy;
EXECUTE IMMEDIATE :SQLQUERY;

select 'DROP TABLE ' || TABLENAME into "SQLQUERY" from dummy;
EXECUTE IMMEDIATE :SQLQUERY;

end;
Code

As seen in following screenshot, I have created temporary table dynamically using SQLScript on SAP HANA database and query data for demo.

dynamically created local temporary data from existing SAP HANA database table



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.