Skip to Content
Author's profile photo Former Member

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;

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann

      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

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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