Skip to Content

Selecting values into scalars/tables

Here’s just a small snippet of code that might save you a bit of a headache as your try to work out the correct syntax for SELECT… INTO… when it comes to scalar variables and tables.

As you’ll note, the syntax isn’t the same nor is it interchangeable.

Cheers,

Jody

DROP TABLE “EXTAB”;

CREATE COLUMN TABLE “EXTAB” (“FIELD” INTEGER);

CREATE PROCEDURE “EXPROC” AS

“EXFIELD” INTEGER;

BEGIN

          — following syntax doesn’t work

          — SELECT 5 INTO “EXTAB” FROM DUMMY;

          — SELECT 5 FROM DUMMY INTO “EXFIELD”;

          SELECT 5 INTO “EXFIELD” FROM DUMMY;

          SELECT 5 FROM DUMMY INTO “EXTAB”;

END;

2 Comments
You must be Logged on to comment or reply to a post.
  • Hey Jody,

    worth mentioning here is that

    SELECT … INTO <table> is actually short for

    INSERT into <table> (SELECT … FROM DUMMY);

    So it persists the data in the table – something one may not expect from a SELECT statement.

    Thus, I’d discourage the usage of SELECT INTO <table> for the sake of comprehensible code.

    BTW: did you find this form documented anywhere? I didn’t…

    – Lars

    • Hi Lars,

      It’s syntax I’m familiar with from previous work so never really looked through documentation, but don’t see it listed in the HANA SQL guide.

      Cheers,

      Jody