3

I want to insert ten columns in a table in SQL HANA Procedure.

The following is what I have done and it seems pretty okay to me but for some reason the system is throwing syntax error.

What I want: 10 new columns named Col_1, Col_2..Col_10 each of type INT.

BEGIN
--DECLARE arr INT ARRAY ;
DECLARE i INT ;
DECLARE str1 STRING;
DECLARE str2 STRING;

str1:= 'Col_';


for i in 1 ..10 do 
     str2 := :str1 || :i;
     exec 'ALTER TABLE "Table_Name" ADD (:str2 INT)';
end for;



END;

I have tried using the same alter command by typing in the column name in the ADD() instead of using a variable and it works just fine.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Muskaan
  • 55
  • 2
  • 9
  • Why, exactly, would you want to do something like this...? This seems like way too much work to do when you could just copy/paste the `ALTER TABLE` statement 10 times... – Siyual Sep 11 '17 at 15:14
  • 1
    There is a requirement to create columns dynamically and the number of columns would come from some other variable. Actually, I need to split comma-delimited string values (which is dynamic again) into columns. – Muskaan Sep 11 '17 at 15:15
  • 1
    So you're going to add 10 columns to this poor table every time you run this proc? You should seriously reconsider your database design... this cannot sustain itself. – Siyual Sep 11 '17 at 15:17
  • No. The addition will be just one time. Let me try to explain it again. I have the following fields:| (a,b,c) (d,e,g,f) (r,t,y,....,k) random number of comma separated values. So, what I have to do is the create the maximum number of columns based on this and fill in the respective individual fields. – Muskaan Sep 11 '17 at 15:20
  • 1
    This change could have been made in the database by doing 10 alter statements in the time it took to make this post... – Jacob H Sep 11 '17 at 15:22
  • Dude. The value is not fixed at 10. I just took the number to see if the code works. – Muskaan Sep 11 '17 at 15:24
  • @Muskaan If it's a one-time thing, why do you need a stored proc to do it? Why can't it be done with 10 alter statements? The number of columns is irrelevant. I'm not trying to argue, just trying to point out that this very much looks like [an XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). It sounds like either a) you're making this far more complicated than it needs to be. Or b) you're planning on adding more and more columns to this table to accommodate for additional records (when you should be using a bridge table for that). – Siyual Sep 11 '17 at 15:25
  • I will rephrase again. I have a column which has string of values in a field. Say, (a,b,c,d) in 1st, (a,b,c) in 2nd, (a,b) in 3rd. I want to split these values into the new columns. Now the maximum number of strings in any column will decide how many new columns will be created, which varies according to the incoming data. Hope you get it now. – Muskaan Sep 11 '17 at 15:29

1 Answers1

0

It looks like poor schema design, anyway try to concatenate it:

BEGIN
--DECLARE arr INT ARRAY ;
DECLARE i INT ;
DECLARE str1 STRING;
DECLARE str2 STRING;
DECLARE str3 STRING;

str1:= 'Col_';

for i in 1 ..10 do 
     str2 := :str1 || :i;
     str3 := 'ALTER TABLE "Table_Name" ADD ('|| :str2 ||' INT)';
     exec str3;
end for;

END;

If it is one time I would simply use explicit multiple alter statements:

ALTER TABLE "Table_Name" ADD (Col_1 INT);
ALTER TABLE "Table_Name" ADD (Col_2 INT);
ALTER TABLE "Table_Name" ADD (Col_3 INT);
--...
ALTER TABLE "Table_Name" ADD (Col_10 INT);
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275