Search Oracle Blogs
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:
In PL/SQL there is an workaround to get similar solution:
Cheers, Paweł
Let me show my idea of SQL improvement, that I would like to see. Traditional insert works like that:
insert into table_aWhat I would like to have is something like that:
(column_1, column_2, column_3, column_4)
values
(value_1, value_2, value_3, value_4);
insert into table_aWith 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.
set column_1 = value_1
,column_2 = value_2
,column_3 = value_3
,column_4 = value_4;
In PL/SQL there is an workaround to get similar solution:
declareBut it's PL/SQL not pure SQL. I really would like to have this syntax in next Oracle release...
v_row table_a%rowtype;
begin
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;
end;
Cheers, Paweł
Subscribe to:
Post Comments (Atom)
7 comments:
I like it. I'd even be happy with something like
INSERT INTO table_a
(col_a) VALUES (val_a)
(col_b) VALUES (val_b)
I won't hold my breath though.
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.
Patrick
Good to know that my idea is reasonable for You and that I'm not the only one who see it useful.
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.
Venkat
Venkat,
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ł
Just like in MySQL in other words... ;)
T Rex,
Thanks for sharing this info. I do not use MySQL and I didn't know that any DB has it already.
Post a Comment