Post reply

Insert with id of inserted row

  • July 22, 2011 at 11:42 pm

    #244837

    Hi SQL Server Central:

    I am building a job card application that uses 7 different tables. Using ASP.NET I am building a form that is going to insert the data into all this tables one at a time. The problem that I am facing at the moment is that upon insertion of the data into the 3rd table the 4th table needs the Id of the 3rd one to do the insert so that records match. After the insert I will like to keep on adding records to the 4th table matching with the newly inserted row in the 3rd table. Any ideas on how to do this? i have been reading about scope_identity(), @@identity, Output clause but I don't have any idea of how to use it or if I am in the right direction. At the moment I don't know if I need a stored procedure or a trigger.

    Thanks in advance,

    Confucius

  • July 23, 2011 at 3:01 am

    #1357397

    Please don't call us "blokes". This is not a chat room for kids but a professional forum.

    Regarding the issue you're struggling with:

    I recommend to call a stored procedure with the values you want to insert together with the OUTPUT approach.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 23, 2011 at 3:07 am

    #1357398

    Thanks LutzM for your reply. Just to make you happy I changed the greeting. Could you please illustrate the use of the output clause?

  • July 23, 2011 at 3:54 am

    #1357408

    http://msdn.microsoft.com/en-us/library/ms177564%28v=SQL.100%29.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 23, 2011 at 4:13 am

    #1357410

    Please have a look at the examples provided in BooksOnLine, the SQL Server help system.

    If you're struggling with modifying it to meet your requirements please post some ready to use sample data as described in the first link in my signature together with your expected result.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 23, 2011 at 4:16 am

    #1357411

    Thanks.

    It is what I was looking for.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply