locked
How to grab the last inserted record's ID from a table? RRS feed

  • Question

  • I have a web form that uses SQLDataSource control to insert the data into a table called Computers. Then in the FormView_ItemInserted method, I have a SQL insert statement that will insert the ID of the just inserted computer and the ID of the software into a separate table called Tracker. My question is, how do I grab the ID of the newly inserted record from the Computer table so I can insert that computer ID to my Tracker table?
    Thursday, July 16, 2009 4:08 PM

Answers

  • Just to add my 2 cents to the discussion, I completely agree with the person who suggested stored procedures, they are far easier to deal with because no matter what kind of key you use, there is a way to implement your need. I know where sqlguru (sp?) is coming from, but I particularly like artificial keys as my implementation key. I ALWAYS include some form of natural key as well, but sometimes even this is created behind the scenes using some other process. A good example is an account number. These are usually numbers with some form of check digit, but they have no real "natural" value either. I go with one of a few methods myself: 1. Use scope_identity() to get the new value or the OUTPUT clause. Either of this is great and does what you need them too. Sometimes scope_identity() won't work because of INSTEAD OF triggers, and the OUTPUT clause won't do it because of your situation where procs aren't used. 2. Use the natural key. If it is an account number, generate that ahead of time, and then use it when saving the new row. It could be some other form of key that is natural to the database system, like a driver's license number, an employee id, etc. In other words, not generated by the database. After the insert, execute: select @newId = tableId from table where natKey = @natKey A bit clunky, but very effective In my procedure generator I have both methods, if I have an INSTEAD OF INSERT trigger, the second method is used. All tables have a UNIQUE constraint as well as a PRIMARY KEY, so it works every time and the client needn't know the difference.
    Louis Blog: http://sqlblog.com/blogs/louis_davidson Book: http://drsql.org/ProSQLServerDatabaseDesign.aspx
    • Marked as answer by Zongqing Li Thursday, August 20, 2009 9:21 AM
    Friday, July 17, 2009 2:44 AM

All replies

  • You can use the OUTPUT clause:
    http://msdn.microsoft.com/en-us/library/ms177564.aspx

    Plamen Ratchev
    • Proposed as answer by Naomi N Monday, August 9, 2010 12:21 AM
    Thursday, July 16, 2009 4:13 PM
  • Thanks for the reply. However, I'm not sure if the page you linked me has any good examples. The Insert part of the Computer table is all done by the SqlDataSource control. From the examples in the page you gave me seems like the Output is used in combination with DELETE/UPDATE/INSERT statement. Basically, after the SqlDataSource control inserts the computer name into the Computer table, I want to get grab that particular ID so I can then manually insert into the Tracker table using code behind SQL Insert statement.
    Thursday, July 16, 2009 4:37 PM
  • Use a real key instead of a physical locator id.  Valid keys are computer name, mac, ip etc
    • Edited by sqlguruu Thursday, July 16, 2009 4:47 PM
    Thursday, July 16, 2009 4:43 PM
  • You can use a stored procedure with parameter to implement the insert. Then inside use INSERT with OUTPUT to get the inserted value and return it back. If this is IDENTITY column then SCOPE_IDENTITY can help too (assuming a single row is inserted). Here is example on using parameters with SqlDataSource, as well as demonstration of using SCOPE_IDENTITY to get the last inserted IDENTITY value:
    http://msdn.microsoft.com/en-us/library/z72eefad.aspx
    Plamen Ratchev
    Thursday, July 16, 2009 4:51 PM
  • If you are inserting one record, and it's automatically generated,you can use the following
    DECLARE @var int
    INSERT INTO TABLE(Columns)
    VALUES (Whatever)
    SELECT @var = SCOPE_IDENTITY()
    

    AE, MCTS
    Thursday, July 16, 2009 4:54 PM
  • sqlguru

    Please answer the question before making suggestions.

    Also, the term is "natural key" not "real key".

    Your examples of valid are flawed. All those can change easily. While computer name usually stays the same, it is very easy to change. mac is many times implemented in software. So, for example, using this to identify a router would be a bad idea. ip is usually dynamic, either because of DHCP or backup servers that take over ips of failed servers automatically.

    In general, unless the data itself, *by definition*, is unique, it is a bad idea to use a pseudo-natural key because it most likely is unique. Examples of data that must be unique are, having a TABLE recording a bank accounts balance, the routing number and bank account #. Or, a lookup table with a mneumonic. Otherwise, a generated id is best. Because the only thing guaranteed to be unique about a record, is that it is a new record. The data inside it often does repeat and thus would not be unique on its own.
    Thursday, July 16, 2009 4:59 PM
  • I would wrap the logic in a stored procedure and call that from the application. This way you can handle all of the inserts and code logic within the database layer.  I would also use the OUTPUT clause as it is more scalable in my opinion than scope_identity.  Below is a sample of how to use output.

    DECLARE @t TABLE ( 
    Id INT IDENTITY(1,1),
    Amount decimal(10,2)
    )
    
    DECLARE @t2 TABLE(
    Id INT PRIMARY key,
    Amount decimal(10,2)
    )
    
    --results table
    DECLARE @results TABLE(
    INS_Id INT
    );
    
    INSERT INTO @t OUTPUT inserted.id INTO @results VALUES(50.00)
    
    INSERT INTO @t2 (id)
    SELECT INS_id FROM @results
    
    SELECT * FROM @t
    SELECT * FROM @t2
    
    

    http://jahaines.blogspot.com/
    Thursday, July 16, 2009 5:05 PM
  • Wrong, look up the definitions.
    I meant a real key in comparison to a physical locator as there are several types of keys (natural, artificial etc).

    Natural keys can change that's why we have cascades for (ex: ISBN-10 transition to ISBN-13). Natural keys can be validated. Can you validate the number 1 vs a mac address?

    The IP address would be a artificial key because it cannot be validated externally, but can be validated within your company.
    The computer name would be a artificial key because it cannot be validated externally, but can be validated within your company.
    The mac address (equivalent to a VIN) is a natural key because it can be validated globally.

    Mac address can be spoofed, the question is....to what extent do you trust the source?



    Thursday, July 16, 2009 5:13 PM
  • >Wrong, look up the definitions.

    Where?

    I searched for it on Google: http://www.google.com/search?q=%22real+key%22+%22natural+key%22 and didn't find too much except some rant by Celko against using IDENTITY.

    >The IP address would be a artificial key because it cannot be validated externally, but can be validated within your company.

    Only to an extent. Companies that use DHCP won't have this. And, if they use non RFC-1918 addresses and they change providers, their assigned block changes.

    >The computer name would be a artificial key because it cannot be validated externally, but can be validated within your company.

    Not really. Anyone can change their computer name. Even if they are using Windows and restrict that, a person could come in with a laptop from home.

    >The mac address (equivalent to a VIN) is a natural key because it can be validated globally.
    >Mac address can be spoofed, the question is....to what extent do you trust the source?

    The point is, the MAC is not a natural key. Unless you are tracking where or what a MAC was used for.




    Thursday, July 16, 2009 5:47 PM
  • I meant "real" key to add emphasis that a physical locator id ISN'T a valid key (natural, artificial, surrogate...its none of these).

    (ip, computer name) are all valid within your company IF SUITABLE. Not sure why you are trying to argue with this point, you probably didn't understand it.

    Mac address is a natural key, you can verify it. It is definitely a candidate for a primary key. It has all the properties of a natural key! It seems like you don't know the actual definition of a mac address (http://en.wikipedia.org/wiki/MAC_address).

    Can you (in the real world) verify a printer vs a computer using a mac address?
    Can you (in the real world) verify a printer vs a computer using a magical number 1?


    • Edited by sqlguruu Thursday, July 16, 2009 6:05 PM
    Thursday, July 16, 2009 5:57 PM
  • I am arguing the point because:

    You never answered the OPs question and the examples you gave were flawed. It seems that you have a lot of knowledge. But why not share it nicely instead of barking snippets at people? We can all gain from the former. I'm in these forums to learn SQL Server. When i see comments like the latter, however, it seems like a waste of time. Including your own.

    MAC is not a natural key for a computer. MACs change. Indeed, the only time MAC is a natural key is if you are tracking MACs. Such as, if you have 100 cards, and they are distribute to different people, the MAC TABLE should indeed use the address as the PK. Because, *by definition*, the address is unique to the object being recorded.

    However, a  computer may have no MACs, multiple MACs, or even change MACs. Thus, the MAC does not, by definition, identify a computer. Thus it is a bad idea to use it. The only way to track a computer is by an assigned serial number, and that is no different than IDENTITY. Which is why IDENTITY would be one of the best choices in that case.
    Thursday, July 16, 2009 6:07 PM
  • Again, read the definition of a mac address (read it until you get it) http://en.wikipedia.org/wiki/MAC_address

    Letme even quote it:
    A universally administered address is uniquely assigned to a device by its manufacturer; these are sometimes called "burned-in addresses" (BIA). The first three octets (in transmission order) identify the organization that issued the identifier and are known as the Organizationally Unique Identifier (OUI).[ 2]


    MAC has all the properties of a natural key:
    Contructed from attributes in the entity: Yes, computers (the entity) has unique mac address, natural key is "constructed" from mac address
    Can be verified in the real world: Yes, you can ping it etc
    Can be verified in itself: Yes
    Visible to the User: yes

    Serial numbers are not a valid natural key because there is no standard encoding scheme. Each company manufacturing the computer has different serial numbers and does not conform to any standard.


    The problem was how to get the "physical locator id" value after the insert. The actual solution is, you don't have to IF you used a "real" key.
    Thursday, July 16, 2009 6:17 PM
  • sqlguru,

    What if you have a duplicate MAC address? It might never happen, but it could.


    AE, MCTS
    Thursday, July 16, 2009 6:22 PM
  • I'll say it a third time (though it was already true): All that about the MAC is only valid if you are tracking the cards themselves. If you are tracking computers that is a bad idea. As the same computer may have no MACs, multiple MACs, or a switched MAC.

    The problem was how to get the just INSERTed key. The answer is with OUTPUT or SCOPE_IDENTITY. Both of which work whether or not there is a key.


    Thursday, July 16, 2009 6:27 PM
  • Again, there's an audit trail. For an example, you can verify with a computer manufacturer which model had which mac address. You can then verify whether it's been switched etc. It's externally verifiable.

    It's all about to what extent do you trust your source.

    Regarding MAC duplicates...
    There are no duplicates. The current system can handle 281,474,976,710,656 addresses (look at the encoding scheme)  and when that runs out, they will transition to the EUI-64s scheme. You could have a spoofed mac address but handling that is no different than a physical locator id, to what extent do you trust the source.
    • Edited by sqlguruu Thursday, July 16, 2009 6:42 PM
    Thursday, July 16, 2009 6:39 PM
  • No, All MAC addresses are unique, however they might have additional driver software that allows you to "mask" it. Most NIC chipsets come from like 1 or 2 major companies and are rebranded (cisco, netgear, linksys etc), so it's definetly the driver that masked it. The MAC address is still verifiable.
    Thursday, July 16, 2009 10:37 PM
  • You may review this thread http://forums.asp.net/p/1438956/3256310.aspx#3256310 It deals with the same problem.
    Thursday, July 16, 2009 11:36 PM
  • Woldn't that still be a problem if the driver reports same MAC address for the 20 NICs?

    You are ignoring the "how much do you trust the source" part. A physical locator id does not solve this. The MAC address can still be verified from the ROM, the masking is just a driver level implementation.
    Friday, July 17, 2009 12:05 AM
  • You have a good question here and a common one.  Unfortunately the answer is "it depends."  It depends on several factors.  For exampe, using "Ident_Current" is a good choice, but that only gives you the "most recently inserted id for the table".  So if you have several applications inserting data into a table there can be a bit of a snafu.  Let's say you have Web-based app facing the world, and a C++ app internally.  They both hit the same db.   If you insert a record with the C++ application, and a SPLIT SECOND LATER the Web app inserts a record, and then, a second later with your C++ app you use Ident_Current() you'll end up with the identity of the record inserted by the WebApp.  In other words, Ident_Current() will only return the most recently inserted ID, and NOT necessarily YOUR most recently inserted. 

    Another choice is SCOPE_IDENTITY() which will give you the most recently inserted ID in YOUR session.  However, this might be tricky depening on how your applications connect to the DB and how they release those connections.  See BOL for Ident_Current and Scope_Identity.

    The OUTPUT clause was invented to exactly address your issue.  It is the perfect fit.  Exect that it requires code changes in any existing applications in order to use it.

    Another way is a simple select against your table to get the ID, but you have to construct that query in such a way as to be certain that you're getting the ID of the record you want and no other record.  This can be tricky unless you have some other field or combination of fields that is unique in your table (besides the ID field, of course).

    So, in order of preference (my preference), I recommend you investigate   1)  OUTPUT,  2)  IDENT_CURRENT(), 3) SCOPE_IDENTITY()

    Hope this helps and doesn't muddle the waters too much.

    Cheers.
    PAC
    Friday, July 17, 2009 1:36 AM
  • Just to add my 2 cents to the discussion, I completely agree with the person who suggested stored procedures, they are far easier to deal with because no matter what kind of key you use, there is a way to implement your need. I know where sqlguru (sp?) is coming from, but I particularly like artificial keys as my implementation key. I ALWAYS include some form of natural key as well, but sometimes even this is created behind the scenes using some other process. A good example is an account number. These are usually numbers with some form of check digit, but they have no real "natural" value either. I go with one of a few methods myself: 1. Use scope_identity() to get the new value or the OUTPUT clause. Either of this is great and does what you need them too. Sometimes scope_identity() won't work because of INSTEAD OF triggers, and the OUTPUT clause won't do it because of your situation where procs aren't used. 2. Use the natural key. If it is an account number, generate that ahead of time, and then use it when saving the new row. It could be some other form of key that is natural to the database system, like a driver's license number, an employee id, etc. In other words, not generated by the database. After the insert, execute: select @newId = tableId from table where natKey = @natKey A bit clunky, but very effective In my procedure generator I have both methods, if I have an INSTEAD OF INSERT trigger, the second method is used. All tables have a UNIQUE constraint as well as a PRIMARY KEY, so it works every time and the client needn't know the difference.
    Louis Blog: http://sqlblog.com/blogs/louis_davidson Book: http://drsql.org/ProSQLServerDatabaseDesign.aspx
    • Marked as answer by Zongqing Li Thursday, August 20, 2009 9:21 AM
    Friday, July 17, 2009 2:44 AM
  • Sorry, but IDENTITY is not a artificial key nor a surrogate. It is the physical location of the row within the table. A account number is an artificial key but it can be confirmed/verified with the accounting department (parts of it can be verified etc). Can you verify a magical number 1 with the accounting department?

    Why would you generate an account number? Isn't that what the accounting department does? Again, you should not construct some kind of artificial key if you can find a artificial key within your company or a industry standard. If there is no artificial key within your company and no industry standard, you can create a composite key that can unique identify instead of trying to hash a artificial key.

    The problem with composite keys is that the number of attributes required to identify an entity uniquely can be large. This is a major implementation flaw of current RDBMS. The foreign keys referencing a composite key have to be duplicated in all referencing tables. Why? For no reason. Instead of having the RDBMS deal with the physical implementation between the referencing and referenced tables, you have to manually deal with the physical implementation. The solution is pointer-based foreign keys (not the network model) and it completely solves the problem of using a physical locator id. This is what Sybase-Watcom originally had (only partially and poorly implemented) and cascades were instant.

    See my feedback for SQL Server
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=472957&wa=wsignin1.0


    • Edited by sqlguruu Friday, July 17, 2009 11:02 AM
    Friday, July 17, 2009 10:51 AM