How to insert into a table with just one IDENTITY column (SQL Express)

Question

Pretty much the same as this question.

But I can't seem to get this to work with SQL Server Express in Visual Studio 2008.

Same Table layout, One column with identity and primary key.

Solution

 INSERT INTO dbo.TableWithOnlyIdentity DEFAULT VALUES

This works just fine in my case. How are you trying to get those rows into the database? SQL Server Mgmt Studio? SQL query from .NET app?

Running inside Visual Studio in the "New Query" window, I get:

The DEFAULT VALUES SQL construct or statement is not supported.

==> OK, so Visual Studio can't handle it - that's not the fault of SQL Server, but of Visual Studio. Use the real SQL Management Studio instead - it works just fine there!

Using ADO.NET also works like a charm:

using(SqlConnection _con = new SqlConnection("server=(local);
                             database=test;integrated security=SSPI;"))
{
    using(SqlCommand _cmd = new SqlCommand
            ("INSERT INTO dbo.TableWithOnlyIdentity DEFAULT VALUES", _con))
    {
        _con.Open();
        _cmd.ExecuteNonQuery();
        _con.Close();
    }
}   

Seems to be a limitation of VS - don't use VS for serious DB work :-) Marc

Comments

[Thomas Sandberg]

Trying to run it from the visual studio designer(query). get an error message. Don't remember the exact text now.

[Misko]

The table only has 1 column, and it is the identity.

[Thomas Sandberg]

Thanks, this works like a charm in the app. Can't trust VS to do everything... Obviously.