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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to safely get last record inserted?

Author  Topic 

token
Posting Yak Master

133 Posts

Posted - 2012-12-26 : 17:39:43
So lets imagine I am building an eBay Auction database for the first time using SQL Server 2012.

Someone creates an auction listing and this is inserted into the database and given a new unique AuctionID. Along with the insertion of the auction, there are many other pieces of data that need to be stored and associated with the auction e.g. images, product properties, etc.

There is a table that holds basic Auction data, and then other tables to store all the other pieces of information that allow for a many-to-many relationship with the Auction.

Now then, in the other tables that hold data about the Auction there would at least be one column that contains the AuctionID and one other column that holds some other info e.g. product specifications.

My way of achieving this (so far) is to first do the insertion of the basic Auction listing and let the database give it a new AuctionID. Fantastic. Next I do this:


SELECT TOP 1 AuctionID FROM Auction
ORDER BY Timestamp DESC


This should return the AuctionID that was last inserted, and if run immediately after the insertion should be the correct one.

I then take this AuctionID, and use that value when inserting the additional information pieces into the other table therefore creating a many-to-many relationship. Viola!

However, is this the correct way to do this when there are millions of transactions happening at the same time so theoretically insertions might be happening within millisecods of each other. Between an insertion taking place, and me retreiving the last inserted AuctionID, I could end up with the wrong AuctionID.

Can anyone please be kind enough to excuse my newbie post and suggest the best way to do this? I have many SQL books in front of me but can't find anything relating to this.

token
Posting Yak Master

133 Posts

Posted - 2012-12-26 : 17:56:28
I think I understand that using the OUTPUT clause would be a good solution?
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-26 : 19:12:08
Would be something like shown below if you want to use the output clause. That would be a good solution. In my example, the ID column in the main table can be an identity column if you wish to autogenerate the ID. If you do that you also have the option of using SCOPE_IDENTITY instead of the OUTPUT clause
CREATE TABLE #tmpAuctionMain(id INT, product VARCHAR(32));
CREATE TABLE #tmpAuctionDetail(id INT, product_detail VARCHAR(32));

INSERT INTO #tmpAuctionMain
(
id,
product
)
OUTPUT INSERTED.id, 'abcdDetail' AS detail
INTO #tmpAuctionDetail(id, product_detail)
VALUES
(
1,
'abcd'
)

DROP TABLE #tmpAuctionMain;
DROP TABLE #tmpAuctionDetail;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-27 : 00:07:02
if its a batch insert you might be better off dumping results into table variable using OUTPUT clause and then using that table in join to retrieve the new ids generated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

token
Posting Yak Master

133 Posts

Posted - 2012-12-28 : 09:36:31
brilliant thanks guys, i have used the OUTPUT clause and it seems to work so far!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-29 : 00:24:12
cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -