BUG: SELECT INTO Does Not Raise Error with No Column Name

ID: Q105336

The information in this article applies to:

BUG# OS/2: 1769(4.2)
       NT: 489 (4.2)

SYMPTOMS

If a table is created using SELECT INTO, and no column name is given, sp_help <tablename> will return error 515:

attempt to insert the value NULL into column <col_name>, table tempdb.dbo.#sphelptab_.....column does not allow nulls.

Retrievals and updates of the data work fine on the table. Select * on the table shows that some of the columns have NULL column names.


CAUSE

The SELECT INTO statement puts a NULL for the column name when aggregate function is used and no column name is given. A table should not have NULL column names.

For example: 


     select count(*), title_id
     into t
     from titles

will create table t with two columns: NULL, title_id. Sp_help t will give error 515.


WORKAROUND

Make sure the columns names are supplied when using SELECT INTO.

For example: 


     select count=count(*), title_id
     into t
     from titles


STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2 and Microsoft SQL Server version 4.2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words: syscolumns sp_help function 


Keywords          : kbprg kbbug4.20a SSrvServer SSrvWinNT 
Version           : 4.2a | 4.2a
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 17, 1999