Can I select 0 columns in SQL Server?
- by Woody Zenfell III
I am hoping this question fares a little better than the similar Create a table without columns. Yes, I am asking about something that will strike most as pointlessly academic.
It is easy to produce a SELECT result with 0 rows (but with columns), e.g. SELECT a = 1 WHERE 1 = 0.
Is it possible to produce a SELECT result with 0 columns (but with rows)? e.g. something like SELECT NO COLUMNS FROM Foo. (This is not valid T-SQL.)
I came across this because I wanted to insert several rows without specifying any column data for any of them. e.g. (SQL Server 2005)
CREATE TABLE Bar (id INT NOT NULL IDENTITY PRIMARY KEY)
INSERT INTO Bar SELECT NO COLUMNS FROM Foo
-- Invalid column name 'NO'.
-- An explicit value for the identity column in table 'Bar' can only be specified when a column list is used and IDENTITY_INSERT is ON.
One can insert a single row without specifying any column data, e.g. INSERT INTO Foo DEFAULT VALUES.
One can query for a count of rows (without retrieving actual column data from the table), e.g. SELECT COUNT(*) FROM Foo. (But that result set, of course, has a column.)
I tried things like
INSERT INTO Bar () SELECT * FROM Foo
-- Parameters supplied for object 'Bar' which is not a function.
-- If the parameters are intended as a table hint, a WITH keyword is required.
and
INSERT INTO Bar DEFAULT VALUES SELECT * FROM Foo
-- which is a standalone INSERT statement followed by a standalone SELECT statement.
I can do what I need to do a different way, but the apparent lack of consistency in support for degenerate cases surprises me.
I read through the relevant sections of BOL and didn't see anything. I was surprised to come up with nothing via Google either.
