While inserting multiple rows what does the statement 'select 1 from dual' do?

Y.E.P picture Y.E.P · Mar 13, 2013 · Viewed 25k times · Source

While inserting multiple rows into a table using the following style :

insert all

into ghazal_current (GhazalName,Rating) values('Ajab Apna Haal Hota Jo Visaal-e-Yaar Hota',5)
into ghazal_current (GhazalName,Rating) values('Apne Hothon Par Sajana Chahta Hun',4)
into ghazal_current (GhazalName,Rating) values('Shaam Se Aankh Mein Nami Si Hai',4)
into ghazal_current (GhazalName,Rating) values('Tumhe Yaad Ho Ke Na Yaad Ho',3)

select 1 from dual;

What does the statement select 1 from dual mean ? What is it here for ?

Answer

Roney Michael picture Roney Michael · Mar 13, 2013

DUAL is a built-in table, useful because it is guaranteed to return only one row. This means DUAL may be used to get pseudo-columns such as user or sysdate, the results of calculations and the like. The owner of DUAL is SYS but it can be accessed by every user. DUAL is well-covered in the documentation. Find out more.

In your case, SELECT 1 FROM DUAL; will simply returns 1. You need it because the INSERT ALL syntax demands a SELECT clause but you are not querying the input values from a table.