how to add autonumber (increment) unique ID to sqlserver

Hi,

I am a newbie learning sql server. I used to use Access table where I set the primary ID (int) as autonumber. Every insert will generate a new ID, which is incremented by 1.

How do I add an INSERT statement to add the incrementing primary ID in sql server. I noticed that you can't set the ID to autonumber as you do in Access. I read that you can set the ID as identity, which can auto increment.

How do I handle this in the INSERT cmd. The only thought is to call a sql cmd with a max on the current id and increment by 1 in vbscript. Afterwards, add the increment ID into the INSERT call.

Is there an easier way to do this?

Thanks,

JohnHi John,

Correct. You can't write to an identity field (and I didn't know you could in Access). You don't write to it, so don't include that field in your insert statement.

If you need the new ID number, return the value from the SQL Server SCOPE_IDENTITY function after you insert the row, either by returning it from your stored procedure or immediately after doing the insert. And you can batch them together so that you have only one round trip to the SQL Server server.

And there are lots of ways to do this, depending on the needs of your application.

Is that enough information? If not, just ask. It's pretty straightforward once you get to know SQL Server a bit.

Don