Can I select 0 columns in SQL Server?

Question

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.

Answer 1

Try

INSERT INTO Bar SELECT * FROM Foo

instead of

INSERT INTO Bar () SELECT * FROM Foo

However the datatype and number of columns need to match (actually the data types need to be able to implicitly convert)

Or do you want this?

CREATE TABLE Bar (id INT NOT NULL IDENTITY PRIMARY KEY)
INSERT INTO Bar DEFAULT VALUES

or

CREATE TABLE Sequence2 (ID INT IDENTITY not null PRIMARY KEY,
            Somedate DATETIME DEFAULT GETDATE() not null,
            SomeID INT DEFAULT 0 not null)
GO

INSERT INTO Sequence2 DEFAULT VALUES

See also: How to insert values into a table with only an identity column

Answer 2

No, what you're asking for is impossible. SQL is a query language, and you are querying precisely nothing in your desired behavior. Whether or not you're retrieving actual table data in your query is irrelevant, but you must retrieve some data. In your COUNT(*) example, you're (obviously) retrieving the count.

The only way to do exactly what you want would be to query the number of rows you're interested in (i.e., COUNT(1) on the desired WHERE clause), then loop over the empty single insert statement that you used in your example for that number of times. There's no single-statement way to go about this.

Answer 3

If I remember correctly, SELECT NULL FROM table_name; is valid syntax, but I don't remember whether this counts as having zero columns or one column (containing NULL for each row).

Answer 4

No it's a SQL limitation. SQL has lots of warts, contradictions and asymmetries. It's unfortunately not mathematically precise or complete unlike the relational theory and algebra it was inspired by and meant to be used to work with.

Answer 5

This is one significant limitation of SQL and one reason why SQL is not relationally complete.

As you may know, Hugh Darwen invented the names DUM and DEE for the two zero-degree relations. SQL has no equivalent of them.

Answer 6

Declare @count as int
select @count = count(*) from mytable
select @count
create table #test (test int identity)
while @count>0
Begin
insert into #test default values
set @count= @count-1
ENd

select * from #test

Does this get you waht you need?

Answer 7

No. A select must have at least one column.

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)

Ah, I think I kind of understand your goal here. You want to insert "placeholder rows" with no actual values (in any column), and those columns will be populated at a later time.

No, this isn't possible. Even if it were to insert 5 "entirely blank" rows, how would you update them later? (if you have nothing to refer to in a WHERE clause)

I refer to this as "spreadsheet thinking". SQL isn't a spreadsheet, its a database.

I would just make an artifical key (INT with identity), and then ignore it later if it serves no purpose for you.