Short Sql insert Statement?

Question

In MySql I could have a table with an auto increment column and insert that way:

INSERT INTO tbl VALUES(null, "bla", "bla");

Is there any way to do this with Microsoft SQL Server Express?

Instead of this:

INSERT INTO tbl(`v1`, `v2`) VALUES ("bla", "bla");  

Thanks

Answer 1

I think you are asking whether you can include a reference to the identity column in your insert statement and pass a null or some other magic value in the Values or Select clause. No, you cannot. You cannot pass a value for the identity column unless you use SET IDENTITY_INSERT [table] ON and pass an actual value.

Answer 2

INSERT INTO tbl VALUES(null, 'bla', 'bla');

Works; use single quotes.

From http://msdn.microsoft.com/en-us/library/ms174335.aspx

"If the values in the Value list are not in the same order as the columns in the table or do not have a value for each column in the table, column_list must be used to explicitly specify the column that stores each incoming value."

Answer 3

In Sql Server, you do not need to specify a value for identity columns (I'm guessing this is what the null in mysql is doing).

So, the short syntax for inserting into your table would be:

Insert Into tbl Values('bla', 'bla')

This works regardless of whether the column is declared to be the primary key of the table and also works for any position of the column in the table. That is, even if the column is in the middle as shown below:

Create Table tbl (
    [Col1] [varchar](50) NULL,
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Col2] [varchar](50) NULL
)

Sql Server will still quite happily interpret the insert statement and take care of the identity insert.

As other posters have mentioned, in Sql Server you actually need to issue the Set Identity_Insert tbl On command to be able to specify a value for identity columns.