Updating multiple culumns with a Stored procedure!

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

General SQL Server Forums

New to SQL Server Programming

Updating multiple culumns with a Stored procedure!

Author

Topic

Zoma
Yak Posting Veteran

76 Posts

Posted - 2010-01-28 : 02:07:35

Hi all

It has been a while i last posted in the forum guess have'nt been too much busy.
My team Web Developers has built an application which is already rolled out and now it is just minor changes on the system. Last week they ask me to Import Data from an Excel spreadsheet,well that was done via the wizard,But the culumns had to be updated with data and differrent tables I had to do single culumn and single table coz i couldnt think of a way to do it. Here is the sample of the code i use for each table:

SELECT No_cIRCUIT FROM HSR_C_Circuit
where Desc_circuit = 'VRYHEID'

SELECT * FROM Terence_Information
WHERE f4 = 'VRYHEID'

UPDATE Terence_Information
SET circuitId = 12
WHERE f4 = (SELECT Desc_circuit FROM HSR_C_circuit
WHERE No_circuit = 12)

select Distinct f4 FROM Terence_Information
where f4 = 'CHATSWORTH'

SELECT * FROM Terence_InformatioN
WHERE wardid = NULL

select * FROM Terence_Information
where f4 = 'CHATSWORTH'

UPDATE Terence_Information
SET f4= 'UMHLATHUZE'
WHERE f4 = 'UMHLATUZE'

UPDATE Terence_Information
SET CIRCUITID = (SELECT c.no_circuit
from HSR_C_Circuit c
where desc_circuit = 'NKANDLA')
where f4 = 'NKANDLA'SHIKISHELA

Now i had to run that for multiple tables and it took me about 3hours. On Friday! Damn! And there was multiple thousands data rows.

Today they asked to investigate the easier way to do that again since im stil a Junior and it has to be done. So i thought of creating a stored procedure that will Update once and i was willing i would get help with the syntax for creating that stored procedure!

And they asked me we have to reset some Identities. Please any one could help especially with the stored procedure.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 02:57:48

so in short you need a stored proc to do three above updates?

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 03:52:29

Looked to me like the SELECTS were to verify the data, mark-one=eyeball! - before running the UPDATES. Could be wrong though ...

Zoma
Yak Posting Veteran

76 Posts

Posted - 2010-01-28 : 04:06:39

quote:
Originally posted by visakh16

so in short you need a stored proc to do three above updates?

yes i want a proc that will do the similar like as below. But now its different Data. And i need to reset the Identies in all tables,is there a way to do it all same time.

Ok the Database is already created and got data on it,This excel spreadsheet comes with different data and we have to update every single field after Importing it to a Table then from that table to the main table and I need a Proc to do it all once a time.The last time this is how i did it i had 2do each every table and that is time wasting. Please if u can help show me the way i can use to run the stored proc to do update all fields all tables same time.
Thank you

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-28 : 04:12:05

The two updates probably took the bulk of the time.


UPDATE Terence_Information
SET circuitId = 12
WHERE f4 = (SELECT Desc_circuit FROM HSR_C_circuit
WHERE No_circuit = 12)

UPDATE Terence_Information 
SET CIRCUITID = (SELECT c.no_circuit
from HSR_C_Circuit c 
where desc_circuit = 'NKANDLA') 
where f4 = 'NKANDLA'SHIKISHELA

Here if you think about it you really are wanting to UPDATE all the columns with the same static value as the subselects will always return the same value.

Either set up variables and assign the return of the inner selects to those variable and update using those (really simple) or rewrite the UPDATE's so that the sub-selects are changed into JOINS. (not quite so easy). If those UPDATEs still take a long time after doing this then you may want to look into the INDEX strategy on the tables.

As to the reset IDENTITY question. My advise is to push back and ask *WHY* they want to do this. It's almost never a good idea.

Luck to you.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 04:12:48

"Importing it to a Table then from that table to the main table"

The tables "in between" are normally referred to as Staging Tables. You might get some useful information searching for that here, or on Google.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources