Can I select 0 columns in SQL Server?

Question

I hope this question will be slightly better than the similar Create a table without columns . Yes, I ask about something that will hit most of all as pointless academic.

It is easy to get a SELECT result with 0 rows (but with columns), for example. SELECT a = 1 WHERE 1 = 0 .

Is it possible to get a SELECT result with 0 columns (but with rows)? , eg. something like SELECT NO COLUMNS FROM Foo . (This is not valid T-SQL.)

I came across this because I wanted to insert multiple 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. 

You can insert one row without specifying column data, for example. INSERT INTO Foo DEFAULT VALUES .

You can request row counting (without extracting the actual column data from the table), for example. SELECT COUNT(*) FROM Foo . (But this 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 it differently, but the apparent lack of consistency in supporting degenerate cases surprises me.

I read the relevant BOL sections and did not see anything. I was surprised to not come up with anything through Google.

Answer 1

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

As you know, Hugh Darwin came up with the names DUM and DEE for two zero-degree relationships. SQL has no equivalent.

Answer 2

No SQL restrictions. SQL has many warts, contradictions, and asymmetries. Unfortunately, it is not mathematically accurate or complete, unlike the relational theory and algebra, which he was inspired and intended to use with them.

Answer 3

No, what you are asking for is impossible. SQL is a query language, and you do not request anything in your desired behavior. Regardless of whether you retrieve the actual table data in the query, it does not matter, but you should get some data. In the COUNT(*) example, you (obviously) retrieve the invoice.

The only way to do what you want is to query the number of rows you are interested in (i.e., COUNT(1) in the desired WHERE ), and then iterate over the empty single-insert operator that you use in your example for that number of times. There is no single way to do this.

Answer 4

Try

 INSERT INTO Bar SELECT * FROM Foo 

instead

 INSERT INTO Bar () SELECT * FROM Foo 

However, the data type and the number of columns must match (in fact, data types must be able to implicitly convert)

Or do you want it?

 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 5

Not. The selection must contain at least one column.

I stumbled upon this because I wanted to insert multiple rows without specifying any column data for any of them. e.g. (SQL Server 2005)

Ah, I think I understand your purpose here. You want to insert “placeholder rows” without actual values ​​(in any column), and these columns will be filled in later.

No, It is Immpossible. Even if you insert 5 "completely empty" lines, how would you update them later? (if you have nothing to reference in the WHERE clause)

I call this "electronic tabular thinking." SQL is not a spreadsheet, its database.

I would just make an artificial key (INT with a personality), and then ignore it later if this is not practical for you.

Answer 6

If I remember correctly, SELECT NULL FROM table_name; is a valid syntax, but I don’t remember whether it considers it to have zero columns or one column (containing NULL for each row).

Answer 7

 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 it work out what you need?