How to do this in SQL Server Stored Procedure 2

[ranchan02]

Hi Everyone:

I'm not sure how to do this in SQL Server, but I thought maybe someone can point me in the right direction. What I want to do is create a Stored Procedure. What I want it to do is read a table (update_table) and if that record exist in another table (master_table) I want to update the master_table with the data in the update_table and update the "status" field in update_table if the record was updated or not. I've done this in PL/SQL, but I'm not really sure how to do this in SQL Server. Can someone help me out with this?

 

[gmmastros]

Well, you have a problem because you can only update data in one table at a time.

My suggestion is to insert data in to a temp table where the records match (some key field) between the tables. Inner Join in perfect for this.

Then, update the master table with this data.
Then, update the update table with this data.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[druer]

You'll want to wrap your work with a transaction:

Begin Transaction
Do my work to update table 1
If error encountered do rollback transaction
Do my work to update table 2
If error encountered do rollback transaction
If no error was encountered in either block
Commit Transaction

So that if you complete the updates to Table1 and something goes wrong with the update for Table2 all of the work is rolled back in Table1. If that is something you care about. It could be that if you can't update the "update table" you want to leave the master table updates in place.

Just a thought,
Dalton

 

[ranchan02]

Hi George,

So you suggest I code it like this:

----------------
CREATE PROCEDURE dbo.testSP AS

update t2
set
t2.field1 = t1.field1,

from update_table t1
inner join master_table t2
on t1.part# = t2.part#
GO
----------------

 

[gmmastros]

Here's the problem...

You want to update data in the master_table AND update the update_table at the same time. This can't be done in a single sql statement, but it can be done within a stored procedure.

This is what I had in mind...

Create Procedure UpdateMasterRecordsFromUpdate
As
SET NOCOUNT ON

Select T1.Part#,
       T1.Field1
Into   #StuffToUpdate
From   Update_Table T1
       Inner Join Master_Table T2
         On T1.Part# = T2.Part#

[green]-- At this point, you will have a 
-- temp table (#StuffToUpdate) that you can use
-- to Update the other tables[/green]

Update M
Set    M.Field1 = S.Field1
From   Master_Table M
       Inner Join #StuffToUpdate S
         On M.Part# = S.Part#

[green]-- At this point, you will have  
-- updated the master table[/green]

Update U
Set    U.Status = 'Updated'
From   Update_Table U
       Inner Join #StuffToUpdate S
         On U.Part# = S.Part#

[green]-- Now we are done.[/green]
Drop Table #StuffToUpdate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ranchan02]

Hi George:

I'll give this a try. Thanks for your help! So looking at this script...if the record doesn't exist in the master_table and update_table, the record is ignored for the StuffToUpdate temp table?

 

[gmmastros]

Yes.

It will be ignored because You create the #StuffToUpdate temp table using an inner join on part number. So, only if part number is found in both tables, will the record be included in the temp table.

As you are writing this, I suggest you create the temp table first. Then, before updating, you add, Select * from #StuffToUpdate

Make sure only the correct records are being included. Once you are reasonably confident that ONLY the correct records will be affected, should you write the update code.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CaptainD]

I'm using MS SQL2000 server where you can create temp tables in memory. Works fast and the temp table is gone when the stored procedure is done with less overhead.

DECLARE @tTemp TABLE (iID INT IDENTITY(1,1)
, sFormName char(10)
,ifkID INT
,sAccountNumber nvarchar(20)
,sRequestedBy nvarChar(50)
,sProgramManager nvarChar(50)
,sProgramSupervisor nvarchar(50)
,sItemRequested nvarchar(500)
,cAmountRequested smallmoney
,cAmountApproved smallmoney DEFAULT 0)