Skip to Content
avatar image
Alexander K

Question about sqlscript

Oct 13, 2016 at 07:24 AM | 262 Views

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

SAP HANA
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    avatar image
    Florian Pfeffer
    Oct 13, 2016 at 07:35 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Rich Heilman
    Oct 13, 2016 at 08:32 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 19, 2016 at 08:43 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Alexander K
      Oct 20, 2016 at 04:24 AM

      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.

  • avatar image
    Alexander K
    Oct 13, 2016 at 07:49 AM

    Thanks, Florian.

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

    apparently it is no exist

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Alexander K
    Oct 17, 2016 at 03:04 AM

    Thanks, Rich.

    And which variant is more faster? Union or

    index-based cell access?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Alexander K
    Oct 18, 2016 at 04:53 AM

    Thanks, Rich.

    Now I know how to insert line into table.

    Add comment
    10|10000 characters needed characters exceeded