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

Sample HANA Database with Table Data for SQL Developers


SAP HANA database SQLScript developers can execute given SQL scripts in this tutorial to create a sample HANA database populated with sample data to work hands-on on created database tables to improve their SQL knowledge. I believe to experience SQL programming on HANA database, programmers require more sample databases to play with. On SQL Server, there is a quite number of sample databases which can be downloaded as a database backup and can be restored on data platform easily. Or database developers can find SQL scripts that can be executed to create sample database tables and INSERT commands to fill those database tables with sample data.


In this HANA database tutorial, I want to share SQL script files that programmers can download and execute on their sample databases. Please note that the tables and data are extracted from AdventureWorksLT database and converted to SQLScript to use in HANA database.

Some of the tables for our sample HANA database shared in this guide are Product, Customer, Address and Sales Order related tables. By joining and querying these tables HANA database developers can improve their SQL knowledge.

Before creating tables maybe it is better to check if same table names are used before in your current schema. To be in the safe side, I did not share the DROP TABLE commands in front of each CREATE COLUMN TABLE command. I left this decision to SQL programmers. If there is not a match with table names used in our sample database, SQL developers can create sample tables without any issue.

database diagram for sample HANA database


Data Model for Sample HANA Database

AdventureWorksLT2017 sample database is used on SQL Server by SQL programmers working on that platform as a sample database. In this guide, I tried to convert SQL Server table structures and sample data into HANA database equivalents as much as I can.

For overall DDL (Data Definition Language) statement of the data model, please use sample-database-for-hana

Address table. Please note that the DEFAULT constraints for ModifiedDate and rowguid columns can either be defined in DDS (Data Definition Statement) of Address table CREATE TABLE command or can be explicitely created with ALTER TABLE ALTER column syntax.

CREATE COLUMN TABLE Address (
AddressID int Generated By Default as IDENTITY(start with 1 increment by 1) NOT NULL,
AddressLine1 nvarchar(60) NOT NULL,
AddressLine2 nvarchar(60) NULL,
City nvarchar(30) NOT NULL,
StateProvince nvarchar(50) NOT NULL,
CountryRegion nvarchar(50) NOT NULL,
PostalCode nvarchar(15) NOT NULL,
rowguid varbinary(16) NOT NULL DEFAULT SYSUUID,
ModifiedDate datetime NOT NULL DEFAULT CURRENT_DATE,
CONSTRAINT PK_Address_AddressID PRIMARY KEY (AddressID),
CONSTRAINT UK_Address_rowguid UNIQUE (rowguid)
);

--ALTER TABLE Address ALTER(rowguid varbinary(16) NOT NULL DEFAULT SYSUUID);
--ALTER TABLE Address ALTER(ModifiedDate datetime NOT NULL DEFAULT CURRENT_DATE);
Code

ProductCategory table.
Please be aware that when SQL programmer runs following SQLScript batch, column table is created and a foreign key is added to the table referencing itself between ProductCategoryID and ParentProductCategoryID columns.

CREATE COLUMN TABLE ProductCategory (
ProductCategoryID int generated by default as identity(start with 1 increment by 1) NOT NULL,
ParentProductCategoryID int NULL,
Name nvarchar(50) NOT NULL,
rowguid varbinary(16) NOT NULL DEFAULT SYSUUID,
ModifiedDate datetime NOT NULL DEFAULT Current_Date,
CONSTRAINT PK_ProductCategory_ProductCategoryID PRIMARY KEY (ProductCategoryID),
CONSTRAINT AK_ProductCategory_Name UNIQUE (Name),
CONSTRAINT AK_ProductCategory_rowguid UNIQUE (rowguid)
);

ALTER TABLE ProductCategory ADD CONSTRAINT FK_ProductCategory_1 FOREIGN KEY (ParentProductCategoryID) REFERENCES ProductCategory(ProductCategoryID);
Code

ProductDescription table

CREATE COLUMN TABLE ProductDescription (
ProductDescriptionID int generated by default as identity(start with 1 increment by 1) NOT NULL,
Description nvarchar(400) NOT NULL,
rowguid varbinary(16) NOT NULL DEFAULT SYSUUID,
ModifiedDate datetime NOT NULL DEFAULT current_date,
CONSTRAINT PK_ProductDescription_ProductDescriptionID PRIMARY KEY(ProductDescriptionID),
CONSTRAINT AK_ProductDescription_rowguid UNIQUE (rowguid)
);
Code

ProductModel table.
Please note that the CatalogDescription field in ProductModel table is in XML data type on SQL Server. Since there is not an XML data type in SAP HANA database, SQLScript developers can use nvarchar(5000) to map XML data on HANA database table columns.

CREATE COLUMN TABLE ProductModel (
ProductModelID int generated by default as identity(start with 1 increment by 1) NOT NULL,
Name nvarchar(50) NOT NULL,
CatalogDescription nvarchar(5000) NULL,
rowguid varbinary(16) NOT NULL default sysuuid,
ModifiedDate datetime NOT NULL default current_date,
CONSTRAINT PK_ProductModel_ProductModelID PRIMARY KEY (ProductModelID),
CONSTRAINT AK_ProductModel_Name UNIQUE (Name),
CONSTRAINT AK_ProductModel_rowguid UNIQUE (rowguid)
);
Code

ProductModelProductDescription table is a mapping table where all valid combinations of product models and descriptions are stored.

CREATE COLUMN TABLE ProductModelProductDescription (
ProductModelID int NOT NULL,
ProductDescriptionID int NOT NULL,
Culture nchar(6) NOT NULL,
rowguid varbinary(16) NOT NULL DEFAULT SYSUUID,
ModifiedDate datetime NOT NULL DEFAULT CURRENT_DATE,
CONSTRAINT PK_ProductModelProductDescription_ProductModelID_ProductDescriptionID_Culture PRIMARY KEY (ProductModelID, ProductDescriptionID, Culture),
CONSTRAINT AK_ProductModelProductDescription_rowguid UNIQUE (rowguid)
);

ALTER TABLE ProductModelProductDescription
ADD CONSTRAINT FK_ProductModelProductDescription_1
FOREIGN KEY(ProductDescriptionID)
REFERENCES ProductDescription (ProductDescriptionID);

ALTER TABLE ProductModelProductDescription
ADD CONSTRAINT FK_ProductModelProductDescription_2
FOREIGN KEY(ProductModelID)
REFERENCES ProductModel (ProductModelID);
Code

And now we are ready to create Product table.
In this table SQLScript developers will face a few data type mapping issues from original SQL Server data types to their corresponding data types on SAP HANA database.
One of the data types that don't exist in SAP HANA but in SQL Server is money data type. When working on HANA database, SQL programmers can use decimal(19,4) for matching money amount values.

Other data type that does not exist in HANA database but exists in SQL Server is varbinary(max). SQLScript developers can use blob data type for such values.

CREATE COLUMN TABLE Product (
ProductID int generated by default as IDENTITY(start with 1 increment by 1) NOT NULL,
Name nvarchar(50) NOT NULL,
ProductNumber nvarchar(25) NOT NULL,
Color nvarchar(15) NULL,
StandardCost decimal(19,4) NOT NULL,
ListPrice decimal(19,4) NOT NULL,
Size nvarchar(5) NULL,
Weight decimal(8, 2) NULL,
ProductCategoryID int NULL,
ProductModelID int NULL,
SellStartDate datetime NOT NULL,
SellEndDate datetime NULL,
DiscontinuedDate datetime NULL,
ThumbNailPhoto blob NULL,
ThumbnailPhotoFileName nvarchar(50) NULL,
rowguid varbinary(16) NOT NULL DEFAULT SYSUUID,
ModifiedDate datetime NOT NULL DEFAULT current_date,
CONSTRAINT PK_Product_ProductID PRIMARY KEY (ProductID),
CONSTRAINT AK_Product_Name UNIQUE (Name),
CONSTRAINT AK_Product_ProductNumber UNIQUE (ProductNumber),
CONSTRAINT AK_Product_rowguid UNIQUE (rowguid)
);

ALTER TABLE Product
ADD CONSTRAINT FK_Product_ProductCategory_ProductCategoryID
FOREIGN KEY(ProductCategoryID)
REFERENCES ProductCategory (ProductCategoryID);

ALTER TABLE Product
ADD CONSTRAINT FK_Product_ProductModel_ProductModelID
FOREIGN KEY(ProductModelID)
REFERENCES ProductModel (ProductModelID);

ALTER TABLE Product ADD CHECK ( ListPrice >= 0.00 );
ALTER TABLE Product ADD CHECK ( StandardCost >= 0.00 );
ALTER TABLE Product ADD CHECK ( Weight > 0.00 );
--ALTER TABLE Product ADD CHECK ( COALESCE(SellEndDate, SellStartDate) >= SellStartDate );
ALTER TABLE Product ADD CHECK ( SellEndDate >= SellStartDate );
Code

Customer table can be created by executing below CREATE TABLE DDS command on a development HANA database. Although the NameStyle column was created as bit data type on SQL Server, we have to convert it to tinyint on HANA database.

CREATE COLUMN TABLE Customer(
CustomerID int generated by default as IDENTITY(start with 1 increment by 1) NOT NULL,
NameStyle tinyint NOT NULL DEFAULT 0,
Title nvarchar(8) NULL,
FirstName nvarchar(50) NOT NULL,
MiddleName nvarchar(50) NULL,
LastName nvarchar(50) NOT NULL,
Suffix nvarchar(10) NULL,
CompanyName nvarchar(128) NULL,
SalesPerson nvarchar(256) NULL,
EmailAddress nvarchar(50) NULL,
Phone nvarchar(25) NULL,
PasswordHash varchar(128) NOT NULL,
PasswordSalt varchar(10) NOT NULL,
rowguid varbinary(16) NOT NULL DEFAULT SYSUUID,
ModifiedDate datetime NOT NULL DEFAULT Current_Date,
CONSTRAINT PK_Customer_CustomerID PRIMARY KEY (CustomerID),
CONSTRAINT AK_Customer_rowguid UNIQUE (rowguid)
);
Code

In our sample HANA database, SQLScript developers will create an additional database table named CustomerAddress

CREATE COLUMN TABLE CustomerAddress (
CustomerID int NOT NULL,
AddressID int NOT NULL,
AddressType varchar(50) NOT NULL,
rowguid varbinary(16) NOT NULL DEFAULT SYSUUID,
ModifiedDate datetime NOT NULL DEFAULT Current_Date,
CONSTRAINT PK_CustomerAddress_CustomerID_AddressID PRIMARY KEY (CustomerID, AddressID),
CONSTRAINT AK_CustomerAddress_rowguid UNIQUE (rowguid)
);

ALTER TABLE CustomerAddress ADD CONSTRAINT FK_CustomerAddress_Address_AddressID FOREIGN KEY(AddressID) REFERENCES Address (AddressID);
ALTER TABLE CustomerAddress ADD CONSTRAINT FK_CustomerAddress_Customer_CustomerID FOREIGN KEY(CustomerID) REFERENCES Customer (CustomerID);
Code

SalesOrderHeader sample HANA database table can be created bby executing below SQLScript command.

CREATE COLUMN TABLE SalesOrderHeader (
SalesOrderID int generated by default as IDENTITY(start with 1 increment by 1) NOT NULL,
RevisionNumber tinyint NOT NULL DEFAULT 0,
OrderDate datetime NOT NULL DEFAULT current_date,
DueDate datetime NOT NULL,
ShipDate datetime NULL,
Status tinyint NOT NULL DEFAULT 1,
OnlineOrderFlag tinyint NOT NULL DEFAULT 1,
SalesOrderNumber nvarchar(25) as ( ifnull( concat('SO', TO_VARCHAR(SalesOrderID) ), '*** ERROR ***')),
PurchaseOrderNumber nvarchar(25) NULL,
AccountNumber nvarchar(15) NULL,
CustomerID int NOT NULL,
ShipToAddressID int NULL,
BillToAddressID int NULL,
ShipMethod nvarchar(50) NOT NULL,
CreditCardApprovalCode varchar(15) NULL,
SubTotal decimal(19,4) NOT NULL DEFAULT 0.00,
TaxAmt decimal(19,4) NOT NULL DEFAULT 0.00,
Freight decimal(19,4) NOT NULL DEFAULT 0.00,
TotalDue AS (ifnull((SubTotal+TaxAmt)+Freight,0)),
Comment nvarchar(5000) NULL,
rowguid varbinary(16) NOT NULL DEFAULT SYSUUID,
ModifiedDate datetime NOT NULL DEFAULT current_date,
CONSTRAINT PK_SalesOrderHeader_SalesOrderID PRIMARY KEY (SalesOrderID),
CONSTRAINT AK_SalesOrderHeader_rowguid UNIQUE (rowguid)
);

ALTER TABLE SalesOrderHeader ADD CONSTRAINT FK_SalesOrderHeader_Address_BillTo_AddressID FOREIGN KEY(BillToAddressID) REFERENCES Address (AddressID);
ALTER TABLE SalesOrderHeader ADD CONSTRAINT FK_SalesOrderHeader_Address_ShipTo_AddressID FOREIGN KEY(ShipToAddressID) REFERENCES Address (AddressID);
ALTER TABLE SalesOrderHeader ADD CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID FOREIGN KEY(CustomerID) REFERENCES Customer (CustomerID);

ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_DueDate CHECK ( DueDate >= OrderDate );
ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Freight CHECK ( Freight >= 0.00 );
-- ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_ShipDate01 CHECK ( ifnull(ShipDate, OrderDate) >= OrderDate );
ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_ShipDate01 CHECK ( ShipDate >= OrderDate );
ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Status_1 CHECK ( Status >= 0 );
ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Status_2 CHECK ( Status <= 8 );
ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_SubTotal CHECK ( SubTotal >= 0.00 );
ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_TaxAmt CHECK ( TaxAmt >= 0.00 );
Code

SalesOrderDetail DDS (Data Definition Statement) CREATE TABLE script to be executed on our sample HANA database is as follows

CREATE COLUMN TABLE SalesOrderDetail (
SalesOrderID int NOT NULL,
SalesOrderDetailID int generated by default as IDENTITY(start with 1 increment by 1) NOT NULL,
OrderQty smallint NOT NULL,
ProductID int NOT NULL,
UnitPrice decimal(19,4) NOT NULL,
UnitPriceDiscount decimal(19,4) NOT NULL DEFAULT 0.0,
LineTotal decimal(19,4) as (IFNULL(( UnitPrice * (1.0 - UnitPriceDiscount) * OrderQty ), 0)),
rowguid varbinary(16) NOT NULL DEFAULT SYSUUID,
ModifiedDate datetime NOT NULL DEFAULT CURRENT_DATE,
CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID PRIMARY KEY (SalesOrderID, SalesOrderDetailID),
CONSTRAINT AK_SalesOrderDetail_rowguid UNIQUE (rowguid)
);

ALTER TABLE SalesOrderDetail ADD CONSTRAINT CK_SalesOrderDetail_OrderQty CHECK ( OrderQty > 0 );
ALTER TABLE SalesOrderDetail ADD CONSTRAINT CK_SalesOrderDetail_UnitPrice CHECK ( UnitPrice >= 0 );
ALTER TABLE SalesOrderDetail ADD CONSTRAINT CK_SalesOrderDetail_UnitPriceDiscount CHECK ( UnitPriceDiscount >= 0 );
ALTER TABLE SalesOrderDetail ADD CONSTRAINT FK_SalesOrderDetail_Product_ProductID FOREIGN KEY(ProductID) REFERENCES Product (ProductID);
ALTER TABLE SalesOrderDetail ADD CONSTRAINT FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID FOREIGN KEY(SalesOrderID) REFERENCES SalesOrderHeader (SalesOrderID) ON DELETE CASCADE;
Code

Data for Sample HANA Database

Sample data for ProductCategory table can be created using below dynamically created INSERT statements. I did not copy all 41 row data below. You can get the full list of ProductCategory Insert commands using ProductCategory-table-data-for-hana-database

insert into ProductCategory (
ProductCategoryID, ParentProductCategoryID, Name, rowguid, ModifiedDate
) values (
1,NULL,'Bikes', 'CFBDA25CDF7147A7B81B64EE161AA37C', '2002-06-01 00:00:00'
);
insert into ProductCategory (
ProductCategoryID, ParentProductCategoryID, Name, rowguid, ModifiedDate
) values (
2,NULL,'Components', 'C657828DD8084ABA91A3AF2CE02300E9', '2002-06-01 00:00:00'
);
insert into ProductCategory (
ProductCategoryID, ParentProductCategoryID, Name, rowguid, ModifiedDate
) values (
3,NULL,'Clothing', '10A7C342CA8248D48A3846A2EB089B74', '2002-06-01 00:00:00'
);
Code

The sample data for HANA database table ProductCategory is extracted from SQL Server AdventureWorksLT2017 sample database using following SQL query

select
'insert into ProductCategory (
ProductCategoryID, ParentProductCategoryID, Name, rowguid, ModifiedDate
) values (
' +
isnull(convert(nvarchar(50),ProductCategoryID),'NULL') + ',' +
isnull(convert(nvarchar(50),ParentProductCategoryID),'NULL') + ',' +
ISNULL('''' + REPLACE([Name],'''','''''') + '''','NULL') + ',' +
ISNULL('''' + REPLACE(convert(nvarchar(50),rowguid),'-','') + '''', 'NULL') + ',' +
ISNULL('''' + convert(nvarchar(50),ModifiedDate,20) + '''','NULL') + '
);' as [Data Export Query]
from [SalesLT].[ProductCategory]
Code

To export sample table data from SQL Server for use in HANA database table ProductDescription, I used following SQL Select statemen. Sorry, since some of the data was corrupt I had to clean them by replacing '?' and repeating space characters etc. Because of that reason, there are some replace and trim commands that will not be used in normal cases.

select
'insert into ProductDescription (
ProductDescriptionID, Description, rowguid, ModifiedDate
) values (
' +
convert(nvarchar(50),ISNULL(ProductDescriptionID,'')) + ',' +
ISNULL('''' + REPLACE(trim(replace(replace(replace(trim(replace([Description],'?','')),' ','<>'),'><',''),'<>',' ')),'''','''''') + '''', 'NULL') + ',' +
ISNULL('''' + REPLACE(convert(nvarchar(50),rowguid),'-','') + '''', 'NULL') + ',' +
ISNULL('''' + convert(nvarchar(50),ModifiedDate,20) + '''','NULL') + '
);' as [Data Export Query]
from [SalesLT].[ProductDescription]
Code

Since data is huge in number (a few hundreds of row data) to paste in this document, database developers can download INSERT statements via ProductDescription-table-data-for-hana-database

For ProductModel table, following SQL query can be used on SQL Server to export sample data for HANA database table.
Just to add some note for SQL programmers who are wondering why there are REPLACE() functions.
The reason is that. In Name column there are text including ' for example "Women's Mountain Shorts"
Such values should be escaped.
This is similar for the originally XML data type column values of CatalogDescription field.

select
'insert into ProductModel (
ProductModelID, Name, CatalogDescription, rowguid, ModifiedDate
) values (
' +
convert(nvarchar(50),ISNULL(ProductModelID,'')) + ',' +
ISNULL('''' + REPLACE([Name],'''','''''') + '''','NULL') + ',' +
ISNULL('''' + REPLACE(convert(nvarchar(max),CatalogDescription),'''','''''') + '''','NULL') + ',' +
ISNULL('''' + REPLACE(convert(nvarchar(50),rowguid),'-','') + '''', 'NULL') + ',' +
ISNULL('''' + convert(nvarchar(50),ModifiedDate,20) + '''','NULL') + '
);' as [Data Export Query]
from [SalesLT].[ProductModel]
Code

The output of the above SQL script which contains INSERT DDS commands to execute on HANA database to populate ProductModel table with sample data can be found at ProductModel-table-data-for-hana-database

Another table in our sample HANA database is ProductModelProductDescription table.
The export SQL query is as follows for this sample table.

select
'insert into ProductModelProductDescription (
ProductModelID, ProductDescriptionID, Culture, rowguid, ModifiedDate
) values (
' +
convert(nvarchar(50),ISNULL(ProductModelID,'')) + ',' +
convert(nvarchar(50),ISNULL(ProductDescriptionID,'')) + ',' +
ISNULL('''' + trim(Culture) + '''','NULL') + ',' +
ISNULL('''' + REPLACE(convert(nvarchar(50),rowguid),'-','') + '''', 'NULL') + ',' +
ISNULL('''' + convert(nvarchar(50),ModifiedDate,20) + '''','NULL') + '
);' as [Data Export Query]
from [SalesLT].[ProductModelProductDescription]
Code

And data exported from SQL Server using above SQL query including INSERT statements is here ProductModelProductDescription-table-data-for-hana-database

One last important sample table is Product table.
I executed below SQL SELECT statement to create INSERT DDS statement for importing this data into HANA database.
One column data is missing in this sample data migration, it is the ThumbNailPhoto column values which are in binary format.

As I will also mention later converting money data type to string or character types on SQL Server requires attention. Otherwise, you can simply lose some data stored in decimal part of the table column.
There are two solutions one is using "trim(str(MoneyDataTypeColumn,19,4))" or using "convert(nvarchar(50),convert(numeric(20,4), MoneyDataTypeColumn))"
I used the later option for StandardCost and ListPrice columns of Product table.

select
'insert into Product (
ProductID, Name, ProductNumber,
Color, StandardCost, ListPrice,
Size, Weight,
ProductCategoryID, ProductModelID,
SellStartDate, SellEndDate, DiscontinuedDate,
ThumbnailPhotoFileName,
rowguid, ModifiedDate
) values (
' +
convert(nvarchar(50),ProductID) + ',''' +
ISNULL(REPLACE([Name],'''',''''''),'') + ''', ''' +
ISNULL(ProductNumber,'') + ''', ' +
ISNULL('''' + Color + '''','NULL') + ', ' +
convert(nvarchar(50),convert(numeric(20,4), StandardCost)) + ', ' +
convert(nvarchar(50),convert(numeric(20,4), ListPrice)) + ', ' +
ISNULL('''' + Size + '''','NULL') + ', ' +
ISNULL(convert(nvarchar(50), Weight), 'NULL') + ', ' +
convert(nvarchar(50), ProductCategoryID) + ', ' +
convert(nvarchar(50), ProductModelID) + ', ' +
ISNULL('''' + convert(nvarchar(50),SellStartDate,20) + '''','NULL') + ',' +
ISNULL('''' + convert(nvarchar(50),SellEndDate,20) + '''','NULL') + ',' +
ISNULL('''' + convert(nvarchar(50),DiscontinuedDate,20) + '''','NULL') + ',' +
ISNULL('''' + convert(nvarchar(max),ThumbnailPhotoFileName) + '''','NULL') + ',' +
ISNULL('''' + replace(convert(nvarchar(50),rowguid),'-','') + '''','NULL') + ',' +
ISNULL('''' + convert(nvarchar(50),ModifiedDate,20) + '''','NULL') + '
);' as [Data Export Query]
from [SalesLT].[Product]
Code

And the sample database table Product content can be created on HANA database by executing the INSERT scripts given here Product-table-data-for-hana-database

For Address table data following SQL script is used to export data from SQL Server.

select
'insert into Address (
AddressID, AddressLine1, AddressLine2,
City, StateProvince, CountryRegion, PostalCode,
rowguid, ModifiedDate
) values (
' +
convert(nvarchar(50),AddressID) + ',' +
ISNULL('''' + REPLACE(AddressLine1,'''','''''') + '''','NULL') + ',' +
ISNULL('''' + REPLACE(AddressLine2,'''','''''') + '''','NULL') + ',' +
ISNULL('''' + REPLACE(City,'''','''''') + '''','NULL') + ',' +
ISNULL('''' + REPLACE(StateProvince,'''','''''') + '''','NULL') + ',' +
ISNULL('''' + REPLACE(CountryRegion,'''','''''') + '''','NULL') + ',' +
ISNULL('''' + PostalCode + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(convert(nvarchar(50),rowguid),'-','') + '''', 'NULL') + ',' +
ISNULL('''' + convert(nvarchar(50),ModifiedDate,20) + '''','NULL') + '
);' as [Data Export Query]
from [SalesLT].[Address]
Code

The exported sample database table data ready to be inserted on HANA database table can be downloaded from Address-table-data-for-hana-database

Customer data can be prepared for SQL Server export using following SQL query.

select
'insert into Customer (
CustomerID, NameStyle, Title,
FirstName, MiddleName, LastName, Suffix,
CompanyName, SalesPerson,
EmailAddress, Phone,
PasswordHash,PasswordSalt,
rowguid, ModifiedDate
) values (
' +
ISNULL(convert(nvarchar(50),CustomerID),'NULL') + ', ' +
ISNULL(convert(char(1),NameStyle),'NULL') + ', ' +
ISNULL('''' + REPLACE(Title,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(FirstName,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(MiddleName,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(LastName,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(Suffix,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(CompanyName,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(SalesPerson,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(EmailAddress,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(Phone,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(PasswordHash,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(PasswordSalt,'''','''''') + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(convert(nvarchar(50),rowguid),'-','') + '''', 'NULL') + ', ' +
ISNULL('''' + convert(nvarchar(50),ModifiedDate,20) + '''','NULL') + '
);
' as [Data Export Query]
from [SalesLT].[Customer]
Code

The SQL Insert DDS statements list can be obtained from Customer-table-data-for-hana-database to use for HANA database import.

select
'insert into CustomerAddress (
CustomerID, AddressID, AddressType, rowguid, ModifiedDate
) values (
' +
ISNULL(convert(nvarchar(50),CustomerID),'NULL') + ', ' +
ISNULL(convert(nvarchar(50),AddressID),'NULL') + ', ' +
ISNULL('''' + AddressType + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(convert(nvarchar(50),rowguid),'-','') + '''', 'NULL') + ', ' +
ISNULL('''' + convert(nvarchar(50),ModifiedDate,20) + '''','NULL') + '
);
' as [Data Export Query]
from [SalesLT].[CustomerAddress]
Code

Customer Address sample data can be created for CustomerAddress HANA table using following SQLScript CustomerAddress-table-data-for-hana-database

HANA database SalesOrderHeader table can be populated with sample data using below INSERT commands.
There are a few issues regarding to migration SQL script here.
First of all, computed columns aka calculated columns in SQL Server or generated column in HANA database, these column values cannot be inserted directly. So I exclude those columns from INSERT command
Second issue is regarding to exporting SQL Server money data. We have to first convert money type to numeric data type then as a second step we have to convert varchar() data type to protect 4 decimal points.
One more solution of this conversion problem is using "trim(str(MoneyField,19,4))" String conversion with additional function arguments then trimming the output of STR function

select
'insert into SalesOrderHeader (
SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate,
Status, OnlineOrderFlag, PurchaseOrderNumber, AccountNumber,
CustomerID, ShipToAddressID, BillToAddressID,
ShipMethod, CreditCardApprovalCode,
SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
) values (
' +
convert(nvarchar(50),SalesOrderID) + ', ' +
convert(nvarchar(5),RevisionNumber) + ', ' +
'''' + convert(nvarchar(50),OrderDate,20) + '''' + ', ' +
'''' + convert(nvarchar(50),DueDate,20) + '''' + ', ' +
ISNULL('''' + convert(nvarchar(50),ShipDate,20) + '''','NULL') + ', ' +
convert(nvarchar(2),[Status]) + ', ' +
convert(nvarchar(1),OnlineOrderFlag) + ', ' +
ISNULL('''' + PurchaseOrderNumber + '''','NULL') + ', ' +
ISNULL('''' + AccountNumber + '''','NULL') + ', ' +
convert(nvarchar(50),CustomerID) + ', ' +
ISNULL(convert(nvarchar(50),ShipToAddressID),'NULL') + ', ' +
ISNULL(convert(nvarchar(50),BillToAddressID),'NULL') + ', ' +
'''' + ShipMethod + '''' + ', ' +
ISNULL('''' + CreditCardApprovalCode + '''','NULL') + ', ' +
convert(nvarchar(50),convert(numeric(20,4),SubTotal)) + ', ' +
convert(nvarchar(50),convert(numeric(20,4),TaxAmt)) + ', ' +
convert(nvarchar(50),convert(numeric(20,4),Freight)) + ', ' +
ISNULL('''' + Comment + '''','NULL') + ', ' +
ISNULL('''' + REPLACE(convert(nvarchar(50),rowguid),'-','') + '''', 'NULL') + ', ' +
ISNULL('''' + convert(nvarchar(50),ModifiedDate,20) + '''','NULL') + '
);
' as [Data Export Query]
from [SalesLT].[SalesOrderHeader]
Code

Downloadable SalesOrderHeader data script that SQL developers can execute and insert data into HANA database table can be found here SalesOrderHeader-table-data-for-hana-database

For sample data of HANA database table SalesOrderDetail, below query can be executed on SQL Server AdventureWorksLT database.

select
'insert into SalesOrderDetail (
SalesOrderID, SalesOrderDetailID, OrderQty,
ProductID, UnitPrice, UnitPriceDiscount,
rowguid, ModifiedDate
) values (
' +
convert(nvarchar(50),SalesOrderID) + ', ' +
convert(nvarchar(50),SalesOrderDetailID) + ', ' +
convert(nvarchar(10),OrderQty) + ', ' +
convert(nvarchar(50),ProductID) + ', ' +
convert(nvarchar(50),convert(numeric(20,4),UnitPrice)) + ', ' +
convert(nvarchar(50),convert(numeric(20,4),UnitPriceDiscount)) + ', ' +
ISNULL('''' + REPLACE(convert(nvarchar(50),rowguid),'-','') + '''', 'NULL') + ', ' +
ISNULL('''' + convert(nvarchar(50),ModifiedDate,20) + '''','NULL') + '
);
' as [Data Export Query]
from [SalesLT].[SalesOrderDetail]
Code

SalesOrderDetail-table-data-for-hana-database


Delta Merge on SAP HANA Database for Data Populated Tables

Right after SQL programmer populates HANA database tables with sample data, it will be a good practise to execute delta merge statements for those sample tables in order to move new data from row store to column store and decrease sizes of tables in memory with more compression options, etc.

Executing below commands will trigger delta merge process for selected HANA database tables.

MERGE DELTA OF "SALESORDERHEADER";
MERGE DELTA OF "SALESORDERDETAIL";
MERGE DELTA OF "CUSTOMERADDRESS";
MERGE DELTA OF "ADDRESS";
MERGE DELTA OF "CUSTOMER";
MERGE DELTA OF "PRODUCT";
MERGE DELTA OF "PRODUCTMODELPRODUCTDESCRIPTION";
MERGE DELTA OF "PRODUCTCATEGORY";
MERGE DELTA OF "PRODUCTDESCRIPTION";
MERGE DELTA OF "PRODUCTMODEL";
Code

Database Views for Sample HANA Database

AdventureWorksLT has 3 database views that SQL programmers can create on SAP HANA database under their schema by executing following CREATE VIEW commands also shared SQLScript create statements at sample-database-views-for-hana

vProductModelCatalogDescription view extracts XML tag values stored in CatalogDescription field of database table ProductModel using XMLEXTRACTVALUE function.
Please note that XMLEXTRACTVALUE is new with SAP HANA 2.0
If you run XMLEXTRACTVALUE function on HANA 1.0 database, you will get following error message:
SAP DBTech JDBC: [328]: invalid name of function or procedure: XMLEXTRACTVALUE

CREATE VIEW vProductModelCatalogDescription
AS
select
ProductModelID,
Name,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Summary') as Summary,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Manufacturer/Name') as Manufacturer,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Manufacturer/Copyright') as Copyright,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Manufacturer/ProductURL') as ProductURL,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/Warranty/WarrantyPeriod') as WarrantyPeriod,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/Warranty/Description') as WarrantyDescription,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/Maintenance/NoOfYears') as NoOfYears,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/Maintenance/Description') as MaintenanceDescription,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/wheel') as Wheel,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/saddle') as Saddle,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/pedal') as Pedal,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/BikeFrame') as BikeFrame,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Features/crankset') as Crankset,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Picture/Angle') as PictureAngle,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Picture/Size') as PictureSize,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Picture/ProductPhotoID') as ProductPhotoID,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Specifications/Material') as Material,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Specifications/Color') as Color,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Specifications/ProductLine') as ProductLine,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Specifications/Style') as Style,
XMLEXTRACTVALUE(CatalogDescription, '/ProductDescription/Specifications/RiderExperience') as RiderExperience,
rowguid,
ModifiedDate
from ProductModel
where catalogdescription is not null;
Code

vGetAllCategories view is used to list parent and child relation of product categories.
Since SAP HANA database SQLScript does not support recursive queries yet, I have modified the original recursive CTE expression in SQL Server to a sub-select statement in HANA database.

CREATE VIEW vGetAllCategories
AS
SELECT
P.Name AS ParentProductCategoryName,
C.Name as ProductCategoryName,
C.ProductCategoryID
FROM (
SELECT
ParentProductCategoryID,
ProductCategoryID,
Name
FROM ProductCategory
WHERE ParentProductCategoryID IS NULL
) AS P
LEFT JOIN ProductCategory AS C
ON P.ProductCategoryID = C.ParentProductCategoryID;
Code

vProductAndDescription view is used to display products and product descriptions by language.

CREATE VIEW vProductAndDescription
AS
SELECT
p.ProductID,
p.Name,
pm.Name AS ProductModel,
pmx.Culture,
pd.Description
FROM Product p
INNER JOIN ProductModel pm
ON p.ProductModelID = pm.ProductModelID
INNER JOIN ProductModelProductDescription pmx
ON pm.ProductModelID = pmx.ProductModelID
INNER JOIN ProductDescription pd
ON pmx.ProductDescriptionID = pd.ProductDescriptionID;
Code

Drop Tables and Views Created on HANA Database

If you want to drop all the sample database tables created for this SQLScript tutorial, developers can use following "DROP TABLE" commands in the given order.
Before dropping tables, database programmers should drop views as first step.

DROP VIEW VGETALLCATEGORIES;
DROP VIEW VPRODUCTANDDESCRIPTION;
DROP VIEW VPRODUCTMODELCATALOGDESCRIPTION;

DROP TABLE CUSTOMERADDRESS;
DROP TABLE SALESORDERDETAIL;
DROP TABLE SALESORDERHEADER;
DROP TABLE ADDRESS;
DROP TABLE CUSTOMER;
DROP TABLE PRODUCT;
DROP TABLE PRODUCTMODELPRODUCTDESCRIPTION;
DROP TABLE PRODUCTCATEGORY;
DROP TABLE PRODUCTDESCRIPTION;
DROP TABLE PRODUCTMODEL;
Code

Since there are "Foreign Key"s created within sample HANA tables referincing other sample tables, a database developer should start with referencing tables before referred tables just like in above order.



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software

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