Same Update In Multiple Jobs - SQL Server Forums

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

Same Update In Multiple Jobs

Author

Topic

dugoneill
Starting Member

11 Posts

Posted - 2009-08-18 : 11:25:33

We currently have a couple of different jobs which need to do the same update just to different tables. The updates are dynamic in that we constantly find new updates which need to be done in each job, and that same update needs to transcend the multiple jobs.

Rather than hard coding this into the jobs I was hoping there is way to use a single table as a look up table where if the value in column A is met, perform the update code in column B.

Then both jobs could be done to just do any updates based on column a, so when a new change is needed it could just be added to the table (so all jobs pointing to that table and thus update would take it into account and do the update within the job as necessary).

Thanks for any insight into a way to do this.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-18 : 14:30:08

Here is an approach:

create table sql_commands(id int identity(1,1), search varchar(255), cmd varchar(max))
insert sql_commands 
select 'a','update testtable_1 set col_a = ''somevalue'' where  1=1' union all
select 'b','update testtable_2 set col_b = ''another value'' where col_b IS NULL'

select * from sql_commands

create table testtable_1 (col_a varchar(255))
create table testtable_2 (col_b varchar(255))

insert testtable_1 values ('no_value')
insert testtable_2 values (null)

select * from testtable_1
select * from testtable_2

declare @sqlcmd varchar(max)
select @sqlcmd=cmd from sql_commands where search='a'
select @sqlcmd
exec(@sqlcmd)

select @sqlcmd=cmd from sql_commands where search='b'
select @sqlcmd
exec(@sqlcmd)

select * from testtable_1
select * from testtable_2

drop table sql_commands
drop table testtable_1
drop table testtable_2

No, you're never too old to Yak'n'Roll if you're too young to die.

dugoneill
Starting Member

11 Posts

Posted - 2009-08-18 : 16:44:27

Thanks WebFred - When trying to create the test table as you did above I get an error message

"Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Max'."

I substituted VarChar(100) since it seemed to be long enough for this test.

Same error and solution in the declare statement.

Those minor issues aside, this did exactly as expected, thank you, excellent starting point.

Now the next question is, can this same structure be used within a CASE Statement, since there will be many rows of data hitting against this in both jobs, and this table will have many rows itself.

The other key here is the table name is included in the cmd column which won't work for the same update to multiple tables.

Basically what I need to do is a serial number pattern update based on part numbers, so in once column I am hoping to have the look up part (the part number) and in the other column the code to update the serial number to what we need it to be (usually we are taking off or adding on prefixes and suffixes which some vendors decided would be fun to put on the lables which are scanned from boxes into the systems we are acquiring the data from - even though the physical serial number on the item is only a part of what is scanned.

Col 1 = PartNo
Col 2 = Is the code to correct the serial number

Example

PartNo | Code
1 | Update SerialNo Set SerialNo = Right(SerialNo,Len(SerialNo) -1)
2 | Update SerialNo Set SerialNo = Substring(SerialNo,4,6)

This same issue is needed in a couple of different processes which unfortunately get the same data format supplied to it (but from different sources, in the same format), then use there own seperate tables from there, so both tables would need this same update with the processes hit the same part numbers.

So table A could have partNo 1 in it as well as table B, and if so we would want the serial number column updated the same way in both processes/tables.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-08-19 : 08:15:22

To be honest it seems easier just to write the updates you need.
You can use functions to help you centralise detection and correction routines.

dugoneill
Starting Member

11 Posts

Posted - 2009-08-19 : 10:05:23

quote:
Originally posted by LoztInSpace

To be honest it seems easier just to write the updates you need.
You can use functions to help you centralise detection and correction routines.

Very well might end up being the case, but would be nice not to have to do it in multiple places for ever new partNo - serialNo pattern identified.

dugoneill
Starting Member

11 Posts

Posted - 2009-08-19 : 15:15:29

quote:
Originally posted by dugoneill

quote:
Originally posted by LoztInSpace

To be honest it seems easier just to write the updates you need.
You can use functions to help you centralise detection and correction routines.

Very well might end up being the case, but would be nice not to have to do it in multiple places for ever new partNo - serialNo pattern identified.

It seems that the only way to do this would be to somehow have the table name be declared within the process, from looking around here, maybe a stored procedure using Dynamic SQL?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-08-20 : 09:03:47

quote:
Originally posted by dugoneill

quote:
Originally posted by LoztInSpace

To be honest it seems easier just to write the updates you need.
You can use functions to help you centralise detection and correction routines.

Very well might end up being the case, but would be nice not to have to do it in multiple places for ever new partNo - serialNo pattern identified.

It sounds like you don't know your system well enough to be embarking on these sort of crazy adventures. Maybe start with a normalisation exercise and then you have it nailed. Sometimes there is no substitute for a bit of hard work :(

dugoneill
Starting Member

11 Posts

Posted - 2009-08-20 : 12:07:35

quote:
Originally posted by LoztInSpace

quote:
Originally posted by dugoneill

quote:
Originally posted by LoztInSpace

To be honest it seems easier just to write the updates you need.
You can use functions to help you centralise detection and correction routines.

Very well might end up being the case, but would be nice not to have to do it in multiple places for ever new partNo - serialNo pattern identified.

It sounds like you don't know your system well enough to be embarking on these sort of crazy adventures. Maybe start with a normalisation exercise and then you have it nailed. Sometimes there is no substitute for a bit of hard work :(

Dont mind the hard work, just wish I wasn't in a situation working with middle ware (for lack of a better term) dealing with what is fed to me from other system.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources