SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to safely get last record inserted?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

token
Posting Yak Master

United Kingdom
133 Posts

Posted - 12/26/2012 :  17:39:43  Show Profile  Reply with Quote
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

United Kingdom
133 Posts

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

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/26/2012 :  19:12:08  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 12/27/2012 :  00:07:02  Show Profile  Reply with Quote
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

United Kingdom
133 Posts

Posted - 12/28/2012 :  09:36:31  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 12/29/2012 :  00:24:12  Show Profile  Reply with Quote
cool

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000