Skip to Content
0

Question about sqlscript

Oct 13, 2016 at 07:24 AM

245

avatar image

Hi, all.

My question is about sqlscript on SAP HANA.

I found an example of inserting to a table.

create table mytab (i int);

insert into mytab values (0);

insert into mytab values (1);

How to insert lines to my declare table variable, such as: declare mytab table (i int)?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Florian Pfeffer
Oct 13, 2016 at 07:35 AM
0

Hello Alexander,

on table variables declared on SQLScript no Insert can be executed. Use a select to fill the table with the required values.

For instance:

declare mytab table (i int);
  
mytab =       select 1 as i from dummy
        union select 2 as i from dummy
        union select 3 as i from dummy;

Regards,

Florian

Share
10 |10000 characters needed characters left characters exceeded
Rich Heilman
Oct 13, 2016 at 08:32 PM
1

I would suggest to use index-based cell access to fill your intermediate table variables. This is a new feature in SPS11.

declare mytab table(i int);

mytab.i[1] = '1';
mytab.i[2] = '2';

Cheers,

Rich

Share
10 |10000 characters needed characters left characters exceeded
kishor basina Oct 19, 2016 at 08:43 PM
0

Here an example :

Create COLUMN TABLE "SCHEMA"."TABLENAME"

("CUSTOMER" VARCHAR(10) PRIMARY KEY NOT NULL, NAME VARCHAR(40));

with the current syntax in the SQL script editor , a new table created with fields CUSTOMER as primary key , NAME .

Now we will fill data manually into the Table using Insert statement.

INSERT INTO "SCHEMA"."TABLENAME" VALUES ('12345', 'CUSTOMERNAME1');

INSERT INTO "SCHEMA"."TABLENAME" VALUES ('12346', 'CUSTOMERNAME2');

INSERT INTO "SCHEMA"."TABLENAME" VALUES ('12347', 'CUSTOMERNAME3');

INSERT INTO "SCHEMA"."TABLENAME" VALUES ('12348', 'CUSTOMERNAME4');

with the above statements , 4 records will be created in the HANA table .

Regards,

Kishor.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks, Kishor.

But I need to append lines into intermediate table variables and I do not need create hana table.

Rich and Florian gave the correct answers.

0
Alexander K Oct 13, 2016 at 07:49 AM
0

Thanks, Florian.

I expect to find special command, such as Append or Insert in Abap, but

apparently it is no exist

Share
10 |10000 characters needed characters left characters exceeded
Alexander K Oct 17, 2016 at 03:04 AM
0

Thanks, Rich.

And which variant is more faster? Union or

index-based cell access?

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Index-based cell access should be faster.

Cheers,

Rich Heilman

0
Alexander K Oct 18, 2016 at 04:53 AM
0

Thanks, Rich.

Now I know how to insert line into table.

Share
10 |10000 characters needed characters left characters exceeded