Insert Update Stored Procedure for a table

  • Comments posted to this topic are about the item Insert Update Stored Procedure for a table

    Prasad Bhogadi
    www.inforaise.com

  • I try to execute the Script but I get a lot of errors

  • Can you please post what errors you are getting?

    Thanks

    Prasad Bhogadi
    www.inforaise.com

  • Never mind it was some spaces that were created when I copy paste the code. I fixed and it works.

    now another question when I run the stored procedure ( exec GenerateInsUpdateScript countries) isnt that suppose to generate another stored procedure and save to my database or it just display it to me????

    Because It just display it to me.

  • It just generates the script as it is a generic script. You need to execute the script and it would create a stored procedure. I thought this is generic and based on the based on the requirements may need little customization. So just generating the script.

    Thanks

    Prasad Bhogadi
    www.inforaise.com

  • I actually use the mygeneration tool to create insert,update,delete, loadbyprimarykey stored procedures which works fine But I thought to give a shot to this one

    any how : good work

  • Thank You, thats a good idea I would probably create a tool which would automate creation of the Insert, Update, Select and Delete Stored Procedures.

    Thanks

    Prasad Bhogadi
    www.inforaise.com

  • So simple and so useful. Thanks a lot!

    Eros

  • Excellent Stored Proc! This will save me a ton of time!

  • hi,

    its soo helpful script for me. so simple to run. really appreaciatable.

    thx

    sreejith

    MCAD

  • I copied the script off of the page, and pasted it into a Notepad++ buffer to look at it. One thing that was kind of odd were the strange characters, which Notepad++ represented by a several "?" characters, which I assume was Notepad++ trying to give me something that went beyond the normal ANSI characterset. All I did was replace each of those "?" with a blank, and that seems to have worked. I ran the script which created the GenerateInsUpdateScript stored procedure.

    In looking at your code I see that your write everything that would generate the CREATE PROCEDURE script into a temporary table and then perform a SELECT against that. However, one thing that I also noticed was that the script that GenerateInsUpdateScript generates isn't quite a complete stored proc. It is missing the BEGIN and END statements for a SP. And also I noticed that between the declaration of the SP and its parameters there is a NULL, as well as there is a NULL between the INSERT statement and the UPDATE statement that GenerateInsUpdateScript comes up with. Still, this is great, because I would hate to do all that GenerateInsUpdateScript does for me! I was thinking that I could just go to the NULL statements and replace them with logic. Something like:

    IF (SELECT COUNT(*) FROM myTable WHERE LastName = @LastName) = 0

    BEGIN

    --Do the INSERT statement

    END

    ELSE

    BEGIN

    --Do the UPDATE statement

    END

    Does that make sense to you?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Hello Rod,

    Thank you for the comments, I see what you were doing! You tried to generate the stored procedure for a table that has no Identity defined and as you might have noticed in my description about the stored procedure "Script asssumes that it has a primary key with auto identity defined".

    If you try generating the stored procedure for a table that has Identity defined for the primary key, you will be able to generate a complete stored procedure script with BEGIN and END.

    Thanking you for your suggestions and I would incorporate the logic to handle tables without IDENTITY column.

    Thanks

    Prasad

    Prasad Bhogadi
    www.inforaise.com

  • Prasad Bhogadi (1/28/2008)


    Hello Rod,

    Thank you for the comments, I see what you were doing! You tried to generate the stored procedure for a table that has no Identity defined and as you might have noticed in my description about the stored procedure "Script asssumes that it has a primary key with auto identity defined".

    If you try generating the stored procedure for a table that has Identity defined for the primary key, you will be able to generate a complete stored procedure script with BEGIN and END.

    Thanking you for your suggestions and I would incorporate the logic to handle tables without IDENTITY column.

    Thanks

    Prasad

    Prasad, I completely missed that line ("Script assumes that it has a primary key with auto identity defined"). WOW, no wonder!! We have several tables, the vast majority of which don't have an identity column in them. Well, even so your script gives me the basics, which I think I can work with.

    Rod

    Kindest Regards, Rod Connect with me on LinkedIn.

  • I realy like this stored procedure. I have a system that uses UPSERTs where first you try to update a row using the primary key and if it the @@rowcount is zero then insert into the table instead.

    Do you think you could do a version that does this instead?

  • I need to insert and update a table that I created from AS400 tp sql server2000. The table gets the information, yet I need to continue to recieve new inserts and updates/changes from as400. How can I do this with DTS?

Viewing 15 posts - 1 through 15 (of 31 total)

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