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
"Missing Right Parenthesis": on Delete Set Null on Update Cascade (Sql/Oracle)
How to Create Calculated Field in MySQL
Subquery in SQL Server Compact Edition
Why Does Comparing a SQL Date Variable to Null Behave in This Way
Insert/Update Tblobfield (Aka Image) Using SQL Parameters
SQL Conversion from Varchar to Uniqueidentifier Fails in View
Oracle: on Duplicate Key Update
Rails Active Query Order by Multiple Values in Specific Order
Combining Rows of Queried Results by Unique Identifier
Delete a Query from Excel Workbook with Vba
How to Do Ms Access Database Paging + Search
How to Select More Than 1 Record Per Day
MySQL Question About Scheduling