Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drawlings
Starting Member
14 Posts |
 Posted - 2014-07-09 : 05:54:47
|
| Good Morning All,I am looking for some advice on the best way to create a ID value when a new record is inserted.The history behind my request is I have been asked to update a old accessdb that have been patched together over years. All its tables are linked to other accessdb's i.e 'Main Tables' etc... So instead of linking the tables to multiple accessdb's I have imported them into SQL. But now my next issue arrives, the 'AutoNumber' that worked in Access doesn't work in SQL. And rather than re write the horrible code in Access VBA I would like to know if a SQL trigger can create a ID value (INT) on Insert?What my thinking is to have a table (TABLEIDs) which has 2 columns (TABLENAME, IDVALUE) and this would store the next ID value for any given table. Could a trigger on insert retrieve the IDValue for a specific table name (tblusers) and add it to the column EMPID? And finally increment IDValue +1.Or am I just being stupid and there is a much easier way (apart from 'uniqueidentifier' as I read somewhere this can issues on multipule servers)ThanksDanMy Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc... |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2014-07-09 : 06:38:27
|
| 1.You can add a column with IDENTITY (1,1) and use SCOPE_IDENTITY() to retrieve the inserted ID , or use Output clause from Insert .2. is SQL 2012 , then you can use SEQUENCE (CREATE SEQUENCE ...)sabinWeb MCP |
 |
|
|
|
|
|
Best Practice obtaining ID Value