Insert Update Stored Procedure for a table

Question

Post reply

Insert Update Stored Procedure for a table

Prasad Bhogadi

SSCrazy Eights

Points: 9235
More actions
October 11, 2007 at 5:25 pm

#375627

Comments posted to this topic are about the item Insert Update Stored Procedure for a table
Prasad Bhogadi
www.inforaise.com

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

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

vblllove Ten Centuries Points: 1273 More actions · Answer 1

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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

Can you please post what errors you are getting?

Thanks

Prasad Bhogadi
www.inforaise.com

vblllove Ten Centuries Points: 1273 More actions · Answer 3

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.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

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

vblllove Ten Centuries Points: 1273 More actions · Answer 5

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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 6

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

Eros Zica SSC Rookie Points: 26 More actions · Answer 7

Eros Zica

SSC Rookie

Points: 26

December 6, 2007 at 12:55 pm

#757837

So simple and so useful. Thanks a lot!

Eros

Marty Comella Grasshopper Points: 21 More actions · Answer 8

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

sreejithsql SSCommitted Points: 1813 More actions · Answer 9

hi,

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

thx

sreejith

MCAD

Rod at work SSC-Dedicated Points: 34006 More actions · Answer 10

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.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 11

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

Rod at work SSC-Dedicated Points: 34006 More actions · Answer 12

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.

Jonathan AC Roberts SSCoach Points: 18345 More actions · Answer 13

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?

L-JEFF SSC Enthusiast Points: 115 More actions · Answer 14

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?