Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Update / Insert Stored Procs

Author  Topic 

PRRSQL
Starting Member

8 Posts

Posted - 2005-10-06 : 13:51:01
SQL Pros.

I am looking for a way to improve efficiency and would like to get your input.

I am writing a VB.NET data migration program. I am process inserts and updates from a legacy system to and populating an SQL Server data warehouse.

I have existing stored proc to perform updates, and inserts. In much of the data I am going to process it may be update data and not insert, but I will not know until I perform a select first then take the update or insert action.

I was going to create a stored proc that performs a select, if @@ROWCOUNT is Zero run the Insert Proc else Run the Update Proc.

I want to keep the insert and update proc separate since I may use these for other code down the road.

Is this best way to go, and does any have examples of calling a stored proc from a stored proc passing parms.

Thank you.

Kristen
Test

22859 Posts

Posted - 2005-10-06 : 14:07:18
We have a optional parameter (@MODE) in our "SAVE" SProcs that allows us to indicate if:

0=INSERT (Must not exist)
1=UPDATE (Must be an existing record)
2=Dunno - Update if you can find one, otherwise Insert. (This is the default)

Often the application knows that, for example, a record was retrieved for the user to modify and save back - so it can provide an @MODE=1 parameter.

There again, the application may now that the user choose "ADD" and we want to safeguard against them choosing a PK that already exists, so it provides an @MODE=0 parameter.

In the SProc if the value is 2 [Insert/Update mode is unknown] we do

SELECT @MODE = CASE WHEN EXISTS (SELECT * FROM dbo.MyTable WHERE MyPK = @MyPKParam) THEN 1 ELSE 0 END

and then we do

IF @MODE = 0
BEGIN
... insert stuff ...
END
ELSE
BEGIN
... update stuff ...
END

Kristen
Go to Top of Page

PRRSQL
Starting Member

8 Posts

Posted - 2005-10-06 : 14:19:05
Very nice idea. I like it better than checking @@ROWCount.

One question, in the body of the IF of the Insert and update logic I would like this to call another proc that just does the insert or update. Is that just being to modular or too anal as the saying goes?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-06 : 15:09:20
Hmmm, I'm not sure I like the fundamental design.

Why would you not know if the row exists or not.

You must have to do retreival of rows. If that was done, then you know they exists, so you would need to perform an INSERT. If the row (keyed) already exists, and someone trys to enter it, you should handle the error and indicate that the row does exists, you might want to even show them the row.

You might even want to check the last update timestamp on the row to see if someone else has modified the row since you grabbed it.

I would be very careful with the type of architecture you are trying to build.



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

PRRSQL
Starting Member

8 Posts

Posted - 2005-10-06 : 15:27:04
I must not have made my post clear. I am performing a select to see if the record exists.

If it does not, insert, if it does, update.

This table and design does not require any type of checking on date time stamps. No users will be updating this table, it will only be updated by a data migration application.

My only question is this…

1. Select (Does the record exist?)
2. No Insert it.
3. Yes, Update it.

My question is regarding Step #2 & #3. Why not take the #2 Insert & #3 Update Logic and place it in another stored proc. This way it is a stand alone insert proc and a stand alone update proc and can be used in that manner from other parts of the application.

In the end...

Proc_select calls Proc_insert if need or calls Proc_update if needed.

When I have created a stored proc to perform a select, insert, update function I always kept it in one proc. I wanted to see if any folks performed this logic in the way I am discussing.

Thank you,
Patrick
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-06 : 16:03:20
Let me ask you...where is the data coming from? Can you describe your process a little better and what you're doing? I get a feeling that you are performing a delta file comparisson process.



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-06 : 16:05:29
quote:
Originally posted by PRRSQL

I am process inserts and updates from a legacy system to and populating an SQL Server data warehouse.



You know, it really does help if you read these things.

You need to employee a set based method rather than this

http://weblogs.sqlteam.com/brettk/archive/2004/04/23/1281.aspx



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

PRRSQL
Starting Member

8 Posts

Posted - 2005-10-06 : 16:09:49
Hi Brett,

The data is coming from a DB2 Database on a midrange system. The database contains all the product data that we want to sell on the web. The web site is really custom sites for many of our customers. I have created these .NET sites already, but now we are working towards a more generic setup to get a custom solution running in a shorter timeframe for customers that want to purchase online. (Some do not they want to call in or use fax, but hey to each his own)

Here is the scope.

1. Migrate items, sizes, prices, shipping overrides, customer pricing overrides from DB2 to SQL Server.
2. Users use a site manager program to associate items to customers (they can only purchase what thier contract states)
3. Customers buy items.

The data migration takes the items from DB2 and items can change such as items are now out of stock, inventory levels, and some minmual content in DB2. This data is processed to a SQL Server Global Item, price, and other tables that are used to select to sell to clients. These global tables are only accessed by a site manager application and end user that selects what items are sold to each customer.

I think what you may be suggesting the delta is to load all the items from DB2 into a temp SQL table then sync to the actual SQL table?

Thank you,
Patrick
Go to Top of Page

PRRSQL
Starting Member

8 Posts

Posted - 2005-10-06 : 16:14:34
Thanks Brett. I will check out your link.

quote:
Originally posted by X002548

quote:
Originally posted by PRRSQL

I am process inserts and updates from a legacy system to and populating an SQL Server data warehouse.



You know, it really does help if you read these things.

You need to employee a set based method rather than this

http://weblogs.sqlteam.com/brettk/archive/2004/04/23/1281.aspx



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-06 : 16:29:17
quote:
Originally posted by PRRSQL

I think what you may be suggesting the delta is to load all the items from DB2 into a temp SQL table then sync to the actual SQL table?



Temp tables are something very specific...I would use a permanent table...I call them staging tables.

And yes, that is what I am suggesting.

And that link will tell you exactly how to do it.



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-10-07 : 01:35:55
"Why would you not know if the row exists or not"

Normally our stuff would but there are times where it doesn't care.

For example, a user can FTP a bunch of images up to the web site. We have a routine that puts their names and height & width into a DB table.

It just calls the "UpSert" Sproc without caring if they already exist.

We probably also have places where there is a need to save a "configuration value" - the last date a report was run, for example. The first time it was run the config. record won't exist, again the caller of the SProc does need to care about that.

But for an end user record maintenance routine then absolutely right! The application needs to care!

"in the body of the IF of the Insert and update logic I would like this to call another proc that just does the insert or update"

This isolates the query plan of the "child" SProc from that of the parent, and this can be a good thing. However, a simple scalar INSERT or UPDATE is unlikely to have issues with query plans, so we don't bother - that keeps all the "UpSert" code in one place.

Kristen
Go to Top of Page
   

- Advertisement -