How to Have a Tableless Select with Multiple Rows

Is it possible to have a tableless select with multiple rows?

Use a UNION:

SELECT 1
UNION
SELECT 2

Looks like this in MySQL:

+---+
| 1 |
+---+
| 1 |
| 2 |
+---+

Use UNION ALL to avoid the loss of non-unique rows.

Simulate table with just select statement

Use the values table constructor:

SELECT v.*
FROM (VALUES ('John Doe', 31, 'pizza'),
('John Doe', 31, 'pizza')
) v(customer_name, customer_age, food);

Notes:

  • Don't use spaces in your column names. They just make it harder to write queries.
  • age is a bad column to have. Instead, you should have date-of-birth.
  • Just repeat the value lists with the values that you want.

Sql select with custom create table

Depending on your version of SQL-Server you can also do...

SELECT
yourTable.*
FROM
(VALUES (11), (12), (15)) AS yourTable(ItemID)

https://msdn.microsoft.com/en-us/library/dd776382.aspx

inserting multiple rows with 1 query

With Access SQL you can't combine two INSERT statements. You could run each of them separately. But if you need to do it with a single statement, you will need to use a more complex query.

INSERT INTO Employee
SELECT '1','b','c'
FROM Dual
UNION ALL
SELECT '2','d','e'
FROM Dual;

Dual is a custom table designed to always contain only one row. You can create your own Dual table using the instructions from this Stack Overflow answer.

However, you don't actually need a custom table for this purpose. Instead of Dual, you can use any table or query which returns only one row.

Different ways of returning hard coded values via SQL

You can use VALUES, aka Table Value Constructor, clause for hardcoded values:

SELECT *
FROM (VALUES('ZZ0027674'),('ZZ0027704'),('ZZ0027707'),
('ZZ0027709'),('ZZ0027729'),('ZZ0027742'),
('ZZ0027750')
) AS sub(c)

LiveDemo

Warning: This has limitation up to 1000 rows and applies to SQL Server 2008+. For lower version you could use UNION ALL instead.

EDIT:

Extra points if someone can show me unpivot ?

SELECT col
FROM (SELECT 'ZZ0027674','ZZ0027704','ZZ0027707',
'ZZ0027709','ZZ0027729','ZZ0027742','ZZ0027750'
) AS sub(v1,v2,v3,v4,v5,v6,v7)
UNPIVOT
(
col for c in (v1,v2,v3,v4,v5,v6,v7)
) AS unpv;

LiveDemo2

SELECT without FROM Clause

Consider the cross join (comma separated tables) of the aggregate queries:

SELECT (query1.CntColumn + query2.CntColumn + query3.CntColumn) AS Qtd
FROM query1, query2, query3

How to return N records in a SELECT statement without a table

The formula (10-COUNT(*)%10)%10 tells you how many rows to add, so you can just select that many dummy rows from an existing dummy table.

SELECT nacha_rows
FROM NACHA_TABLE
UNION ALL
SELECT TOP (SELECT (10-COUNT(*)%10)%10 FROM NACHA_TABLE) REPLICATE('9',94)
FROM master.dbo.spt_values

Table-less UNION query in MS Access (Jet/ACE)

You didn't overlook anything. Access' database engine will allow a single row SELECT without a FROM data source. But if you want to UNION or UNION ALL multiple rows, you must include a FROM ... even if you're not referencing any field from that data source.

I created a table with one row and added a check constraint to guarantee it will always have one and only one row.

Public Sub CreateDualTable()
Dim strSql As String
strSql = "CREATE TABLE Dual (id COUNTER CONSTRAINT pkey PRIMARY KEY);"
Debug.Print strSql
CurrentProject.Connection.Execute strSql
strSql = "INSERT INTO Dual (id) VALUES (1);"
Debug.Print strSql
CurrentProject.Connection.Execute strSql

strSql = "ALTER TABLE Dual" & vbNewLine & _
vbTab & "ADD CONSTRAINT there_can_be_only_one" & vbNewLine & _
vbTab & "CHECK (" & vbNewLine & _
vbTab & vbTab & "(SELECT Count(*) FROM Dual) = 1" & vbNewLine & _
vbTab & vbTab & ");"
Debug.Print strSql
CurrentProject.Connection.Execute strSql
End Sub

That Dual table is useful for queries such as this:

SELECT "foo" AS my_text
FROM Dual
UNION ALL
SELECT "bar"
FROM Dual;

Another approach I've seen is to use a SELECT statement with TOP 1 or a WHERE clause which restricts the result set to a single row.

Note check constraints were added with Jet 4 and are only available for statements executed from ADO. CurrentProject.Connection.Execute strSql works because CurrentProject.Connection is an ADO object. If you try to execute the same statement with DAO (ie CurrentDb.Execute or from the Access query designer), you will get a syntax error because DAO can't create check constraints.



Related Topics



Leave a reply



Submit