Search Oracle Blogs

List of Blogs maintained by Paweł Barut.

Wednesday, September 05, 2007

Alternative Syntax for Insert

Alternative Syntax for Insert

Let me show my idea of SQL improvement, that I would like to see. Traditional insert works like that:
insert into table_a
    (column_1, column_2, column_3, column_4)
    (value_1, value_2, value_3, value_4);
What I would like to have is something like that:
insert into table_a
   set column_1 = value_1
      ,column_2 = value_2
      ,column_3 = value_3
      ,column_4 = value_4;
With this syntax code would be easier to maintain. Especially, when you have to add or delete, or find error in insert to table with many columns. Biggest advantage of this would be close relationship between column name and value that will be set for that column. In INSERT that we know it column name and value are quite far from each other, so even for table with 10 columns it might be hard to find corresponding entries.
In PL/SQL there is an workaround to get similar solution:
    v_row table_a%rowtype;
    v_row.column_1 := value_1;
    v_row.column_2 := value_2;
    v_row.column_3 := value_3;
    v_row.column_4 := value_4;
    insert into table_a values v_row;
But it's PL/SQL not pure SQL. I really would like to have this syntax in next Oracle release...

Cheers, Paweł


Gary Myers said...

I like it. I'd even be happy with something like
(col_a) VALUES (val_a)
(col_b) VALUES (val_b)
I won't hold my breath though.

Patrick Wolf said...

Paweł, that would be a very good enhancement! Especially if you have a lot of columns it's sometimes really hard to identify the correlating value in the VALUES section. Your proposed SET syntax would make it quite clear and it would be similar to UPDATE.


Paweł Barut said...

Good to know that my idea is reasonable for You and that I'm not the only one who see it useful.

Venkatakrishnan said...

I like it too Pawel. But i believe this has not been introduced since it might affect the uniformity in the way insert works. For example, when we want to insert based on data from another table we would have to change the syntax similar to the current one since the set syntax would not make sense there.


Paweł Barut said...

I agree that current syntax is better for inserting many rows by single statement. My proposition is additional syntax for inserting just single row. It should be only considered as alternative to insert into .. values .

Regards, Paweł

T Rex said...

Just like in MySQL in other words... ;)

Paweł Barut said...

T Rex,
Thanks for sharing this info. I do not use MySQL and I didn't know that any DB has it already.


Copyright © Paweł Barut
Printing from DOS to USB Printer