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.

Answer 1

So what happens if you try

 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

Answer 2

Instead of trying to rely on some syntax about "DEFAULT VALUES" as the keyword, let the engine help you with that... How about explicitly trying to set ONE of the values, such as

insert into YourTable ( PickOneField ) values ( "whatever" )

So, even though you are only preping ONE field, the new record by the native engine SHOULD populate the REST of them with their respective default values.