Correct way to insert data into multiple tables (Stored Procedure) RRS feed

  • Question




    I am currently developing my first database driven application and I have stumbled over some quite simple issue. I'll describe my database design first:

    I have one table named images(id (identity), name, description) and one table named albums (id, name, description). Since I'd like to establish a n:n connection between these, I defined an additional table ImageInAlbum (idImage, idAlbum). The relation between these tables works as expected (primary keys, foreign keys appear to be ok).


    Now I'd like to insert data via a stored procedure in sql server 2005 and I'm not sure how this procedure will look like.

    To add a simple image to a given album, I am trying to do the following:

    * Retrieve name, description from the UI

    * Insert a new row into images with this data

    * Get the ID from the newly created row

    * Insert a new row into "ImageInAlbum" with the ID just retrieved and a fixed Id from the current album.


    I know how I would do the first two things, but I am not used to Stored Procedures syntax yet to know how to do the other things.


    Any help is appreciated ... even if it means telling me that I am doing something terribly wrong Smile

    Saturday, November 3, 2007 11:07 AM


All replies

  • Ok, it appears that this is the right way to do it and I also found an example which I could stick to ...

    The solution (for the moment) is:

    Code Block

    ALTER PROCEDURE dbo.InsertNewImage
     @ImageTitle nvarchar(30),
     @ImageDescription nvarchar(100),
     @ImageFilename nvarchar(50),
     @ImageThumbnail image,
     @AlbumID int
      Set Nocount On
      DECLARE @ImageID int
      INSERT INTO Images(ImageTitle, ImageDescription, ImageFilename, ImageThumbnail)
      VALUES (@ImageTitle, @ImageDescription, @ImageFilename, @ImageThumbnail)
      INSERT INTO ImageInAlbum(ImageId, AlbumId)
      VALUES (@ImageID, @AlbumID)


    Saturday, November 3, 2007 12:54 PM
  • Excellent Thomas,


    That looks very good for your first venture with Stored Procedures.


    Improvements you 'might' consider"


    Data Validation -what should happen if the application passes in NULL values?

    Transaction -should both inserts happen correctly or none -or should either one be allowed if the other fails?

    Error checking -should the INSERT into ImageInAlbum STILL be allowed if the INSERT into Images were to FAIL?


    Here are some resouces to help guide you:


    Stored Procedure -Basics

    Stored Procedure -How to Create
    BOL -How to: Create a Stored Procedure (SQL Server Management Studio)

    Stored Procedure -Error Handling, Background

    Stored Procedure -Error Handling, Implementation

    Stored Procedure - Error Handling, Using Try...Catch

    Stored Procedure -For the .NET Developer


    As you gain experience, you will have more questions. Don't hesitate to post them here.


    • Proposed as answer by zubairTechy Monday, June 14, 2010 12:35 PM
    • Marked as answer by Kent Waldrop _ Tuesday, July 20, 2010 4:34 PM
    Saturday, November 3, 2007 6:29 PM
  • I have a similar question



    I have one parent table with a primary key that auto increments. Lets call this table User


    I then have three child tables where the primary key(USERID) needs to be passed to them on a stored procedure as a foriegn key.


    I have got the system auto updating on one child table using the example shown but when i attempt to generate same system for two or three child systems it doesnt compile properly and error messages occur.


    is there a way of modifing this procedure so that three child tables can be automatically inserted when this procedure is called through asp.net





    sql greenboy







    Wednesday, April 23, 2008 9:47 AM
  • As in the example displayed above, capture the SCOPE_IDENTITY (instead of @@IDENTITY) value into a variable, and then use that variable in each of the child tables. Exact same process as displayed -unless of course, you are doing something different and you haven't completely disclosed what you are doing.


    It would be helpful to post the table DDL for each table, as well as the ERROR messages that you are getting.




    Thursday, April 24, 2008 3:12 AM
  • couldnot undrstand..

    please specify clearly in an easy way....


    Friday, April 30, 2010 7:03 PM
  • You may find this blog post helpful of dealing with multi tables insert. Please note, that in SQL Server 2005 and up it may be better to use OUTPUT clause of INSERT statement rather than scope_identity().

    Take a look How to insert information into multiple related tables and return ID using SQLDataSource

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by zubairTechy Monday, June 14, 2010 12:36 PM
    Friday, April 30, 2010 9:08 PM
  • Hi Thomas and hi to all,

      I am a beginner to stored procedure.

    Here i have a doubt in multiple inserts in stored procedure.

      Suppose if one insert fails ,it should not run the next insert.

    I would except it to work like (in the CONCEPT OF COMMIT ROLL BACK) fashion.

    So how do i do that ,can any one help on this issue ?


    Thanks in advance


    Tuesday, July 20, 2010 2:41 PM
  • Hi,

    Please visit the blog referenced in my post. The SP shown may be a bit too complex, but it shows the basic concept - usage of transactions and error handling. It also provides links for further reading on this topic.

    In addition, here is the recent thread http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7619acad-22b6-4a1c-beae-7a1d21bb17b6/#57615d72-7c30-43b1-9ca9-30b258644ffe on the exact same issue.

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, July 20, 2010 3:51 PM