Search code examples
sql-serversql-server-2000

How to structure SELECT from VALUES on MS SQL Server 2000

I have an old application running against SQL Sever 2000. I am trying to use the following as part of a query. It runs well on more recent versions of SQL server, but not on 2000. The error is

"Incorrect syntax near the keyword 'VALUES'."

Is there a syntax that I could use for 2000?

SELECT N FROM(VALUES
        (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
) t(N)
Solution

  • The Values construct wasn't introduces till 2008 , in sql server 2000 you can do something like 

    SELECT N 
FROM(
      SELECT 1 as N UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
      UNION ALL SELECT  7 UNION ALL SELECT 8 UNION ALL SELECT 9           
) t