hi guys i would like to create an auto insert field(ex:s-no) which automatically increments by 1.i want a jdbc code which inserts the numbers automatically(increments by 1 for successful insert). how can i do it. pls help
You don't do this with a JDBC statement. In SQL Server this is an identity column. You create this column in your create table statement. Oracle 8i has something very similar. Basically you create a type int column and tell it you want to be an identity column. In SQL Server, the default increment unit is '1', starting at '0'. This can be changed by providing seed values. If your table is already created, you can copy the existing table by doing a select into 'theTemporaryTable', drop the original table, recreate the original table with an identity column, copy the data back in. When working with a table with an identity column, you do not include the identity column when doing inserts, just the regular columns. The database will automatically create the next value and insert it into the record with the rest of your data. Microsoft's documentation for SQL Server is, for Microsoft, unusual, in that it is absolutely fantastic. However it is online. So... check the SQL Server Boos Online for more info. Without doing it using an identity column, there are any other hoops to jump through. The key is to ensure that the 'next' value you are going to use isn't already taken by another user who is doing a simultaneous update. This won't be an issue with an identity column. Hope this helps
Joined: Dec 11, 2000
More to this: MS Access and IBM's DB2 also support the identity property. All databases do this in their own particular flavour.