Help in writing 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

Help in writing a stored procedure

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-04-13 : 14:32:32

Hi
Could any body please help me writing a stored procedure to update the column Resource, Resource_pool

I have 2 table , for eg , as look up tables by which the column resource and resorce pool has to be updated .

Sample Data :

Here are some of the examples of distinct resource and resource pool i have each with a unique code.

Look up Table 1: Resource_POOL

RESOURCE_POOL_NAME RESOURCE_POOL_CODE
Pod1 1
Pod2 2
FixStatus 3
CRMBuilds 4

Look Up TABLE 2 : RESOURCE

RESOURCE_NAME Resource_CODE
STRUT 1
BTRPOD103 2
BTRPOD204 3
QCTCRMBLD04 4

I have a source table , which has columns HOST_NAME AND RESOURCE NAME which are combinations of resource_pool and resource_names

Here is the source table output with HOST_NAME AND RESOURCE_NAME columns

HOST_NAME RESOURCE_NAME
BTRPOD101 Pod1
BTRPOD204 Pod2
QCTCRMBLD04 CRMBuilds
QCTCRMBLD04 CRMBuilds
NULL STRUT
FIXSTATUS FIXSTATUS

Basically we treat HOST_NAME as Resource_name
and Resource_name AS Resource_Pool_Name

So , for eg , if we take STRUT from the above output , STRUT is an a resource ( Please look into the look up table 2),
but in the above output it is entered in the column resource_name which is treated as a resource_pool.

Another example is FIXSTATUS which is a resource pool(Please look into the Look up table 1) but it is both in host_name and resource_name ,

So i need to create 2 new columns which distinguishes between resource and resource_pool_names.

Either i need to write a stored procedure or a query or a program which actually separates resources and resource_pool_names

Here is the expected output for the above data:

HOST_NAME RESOURCE_NAME RESOURCE RESOURCE_POOL_NAME
BTRPOD101 Pod1 BTRPOD101 Pod1
BTRPOD204 Pod2 BTRPOD204 Pod2
QCTCRMBLD04 CRMBuilds QCTCRMBLD04 CRMBuilds
QCTCRMBLD04 CRMBuilds QCTCRMBLD04 CRMBuilds
NULL STRUT STRUT null
FIXSTATUS FIXSTATUS null FIXSTATUS

Please Help....

rds207
Posting Yak Master

198 Posts

Posted - 2010-04-13 : 17:56:59

Please let me know if the question is not clear ..

I have tried writing some update queries , but looks like update does not work as its just updating either of the columns.

Please Suggest what is the best way to achieve this...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-14 : 00:08:52

your rules are not clear. What was the reason why FIXSTATUS was repeated for HOST_NAME & RESOURCE_NAME? Also how come BTRPOD204 & Pod2 came in same row (they've different ids). how are tables related then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources