This blog post is for all the Oracle developers who keep on asking for the lack of “dual” table in SQL Server. Here is a quick note about DUAL table, in an easy question-and-answer format.
Dual is a table that is created by Oracle together with data dictionary. It consists of exactly one column named “dummy”, and one record. The value of that record is X.
You can check the content of the DUAL table using the following syntax.
SELECT * FROM dual
It will return only one record with the value ‘X’.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dual’.
The reason behind the error shown above is your attempt to SELECT values from DUAL table in SQL Server. This table does not exist in SQL Server. Continue reading for workaround.
select sysdate from dual
None. There is no need of Dual table in SQL Server at all.
select ‘something’ from dual
If you have transferred your code from Oracle and you do not want to remove DUAL yet, you can create a DUAL table yourself in the SQL Server and use it.
Here is a quick script to do the said procedure.
CREATE TABLE DUAL
INSERT INTO DUAL (DUMMY)
After creating the DUAL table above just like in Oracle, you can now use DUAL table in SQL Server.
Reference: Pinal Dave (http://blog.SQLAuthority.com)