Question about sqlscript

[former_member214867]

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)?

[pfefferf · Accepted solution]

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

[RichHeilman]

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

[Former Member]

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.

[former_member214867]

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.

[former_member214867]

Thanks, Rich.

Now I know how to insert line into table.

[former_member214867]

Thanks, Rich.

And which variant is more faster? Union or

index-based cell access?

[RichHeilman]

Index-based cell access should be faster.

Cheers,

Rich Heilman

[former_member214867]

Thanks, Florian.

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

apparently it is no exist