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
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;
Here is the output displayed when I execute above SQLScript SELECT command on SAP HANA Studio on a S/4HANA database
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;
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;
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;
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;
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;
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;
As seen in following screenshot, I have created temporary table dynamically using SQLScript on SAP HANA database and query data for demo.
