CODEWITHSUNDEEP
sqlsql-serveroracle-databaset-sqldual-table
wabregoc
wabregoc

Reputation: 1142

What is the equivalent of the Oracle "Dual" table in MS SqlServer?

What is the equivalent of the Oracle "Dual" table in MS SqlServer?

This is my Select:

SELECT pCliente,
       'xxx.x.xxx.xx' AS Servidor,
       xxxx AS Extension,
       xxxx AS Grupo,
       xxxx AS Puerto
FROM DUAL;

Upvotes: 84

Views: 170468

Answers (6)

Jean-François Savard
Jean-François Savard

Reputation: 21004

In SQL Server, there is no dual. You can simply skip the FROM clause:

SELECT pCliente,
       'xxx.x.xxx.xx' AS Servidor,
        xxxx AS Extension,
        xxxx AS Grupo,
        xxxx AS Puerto

However, if your problem is that you transferred some code from Oracle which references dual, you may create a dummy table:

CREATE TABLE DUAL
(
DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO

Upvotes: 88

Omer
Omer

Reputation: 10274

You don't need DUAL in MSSQLserver

in oracle

select 'sample' from dual

is equal to

SELECT 'sample'

in sql server

Upvotes: 39

eBear
eBear

Reputation: 11

It's much simpler than that. Use literal values to establish data types. Put quotes around column names if they need special characters. Skip the WHERE clause if you need 1 row of data:

SELECT  'XCode' AS XCode
       ,1 AS XID  
       ,'XName' AS "X Name"
       ,'YCode' AS YCode
       ,getDate() AS YID
       ,'YName' AS "Your Name"
 WHERE 1 = 0

Upvotes: 1

Chakradhar
Chakradhar

Reputation: 21

This could be of some help I guess, when you need to join some tables based on local variables and get the information from those tables:

Note: Local variables must have been

Select  @XCode     as 'XCode '
       ,@XID       as 'XID   '  
       ,x.XName    as 'XName '
       ,@YCode     as 'YCode '
       ,@YID       as 'YID   '
       ,y.YName    as 'YName '
From (Select 1 as tst) t
Inner join Xtab x on x.XID = @XID
Inner join Ytab y on y.YID = @YID

Upvotes: 1

HENG Vongkol
HENG Vongkol

Reputation: 941

In SQL Server there is no dual table. If you want to put a WHERE clause, you can simple put it directly like this:

SELECT 123 WHERE 1<2

I think in MySQL and Oracle they need a FROM clause to use a WHERE clause.

SELECT 123 FROM DUAL WHERE 1<2

Upvotes: 3

Lukas Eder
Lukas Eder

Reputation: 221380

While you usually don't need a DUAL table in SQL Server as explained by Jean-François Savard, I have needed to emulate DUAL for syntactic reasons in the past. Here are three options:

Create a DUAL table or view

-- A table
SELECT 'X' AS DUMMY INTO DUAL;

-- A view
CREATE VIEW DUAL AS SELECT 'X' AS DUMMY;

Once created, you can use it just as in Oracle.

Use a common table expression or a derived table

If you just need DUAL for the scope of a single query, this might do as well:

-- Common table expression
WITH DUAL(DUMMY) AS (SELECT 'X')
SELECT * FROM DUAL

-- Derived table
SELECT *
FROM (
  SELECT 'X'
) DUAL(DUMMY)

Upvotes: 7

Related Questions