do I use a stored proc or a query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

do I use a stored proc or a query?

Author

Topic

PGG_CA
Starting Member

24 Posts

Posted - 2010-02-22 : 14:08:32

I need to populate this table:
ID fieldID fieldvalue

The fieldid and fieldvalue are the primary keys. Each ID has 3 fieldIDs that need to be prepopulated, value of which doesn't matter at this time. It could be just the letter 'A'. However, an ID could already have 0 to 3 fields already with a value in the table and that's fine. In the end, I want all 3 fields for an ID with a value.

Existing data in the table is like this:

ID fieldID fieldvalue
6 12a3 active
6 456b support
6 222x 10
10 456b maintenance
13 222x 15
14 12a3 inactive
14 456b support
...

I don't mind if eventually all the field values become 'A' at the end of the query, as long as it's populated with something.

There is another table that houses the fieldIDs:
fieldID fieldlabel
12a3 status
456b position
222x buildingID

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 14:16:37

Can also show us your expected output..for this sample data.

PGG_CA
Starting Member

24 Posts

Posted - 2010-02-22 : 15:17:48

I would like to see each ID with 3 fieldIDs and with fieldvalues. Again the field value does not matter, whichever is easier to do. If it's easier to replace all the values with 'A', for example, is alright with me.

ID fieldID fieldvalue
6 12a3 active
6 456b support
6 222x 10
10 12a3 active
10 456b maintenance
10 222x 12
13 12a3 inactive
13 456b maintenance
13 222x 15
14 12a3 inactive
14 456b support
10 222x 12

PGG_CA
Starting Member

24 Posts

Posted - 2010-02-22 : 15:18:39

Opps..
ID fieldID fieldvalue
6 12a3 active
6 456b support
6 222x 10
10 12a3 active
10 456b maintenance
10 222x 12
13 12a3 inactive
13 456b maintenance
13 222x 15
14 12a3 inactive
14 456b support
ID fieldID fieldvalue
6 12a3 active
6 456b support
6 222x 10
10 12a3 active
10 456b maintenance
10 222x 12
13 12a3 inactive
13 456b maintenance
13 222x 15
14 12a3 inactive
14 456b support
14 222x 12

...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 15:29:58

I'm still not clear with your expected output...But maybe this?

Sample data

declare @t table 
(ID int, fieldID varchar(10),fieldvalue varchar(50))
insert @t
select 6, '12a3', 'active'
union all select 6, '456b', 'support'
union all select 6, '222x' ,'10'
union all select 10, '456b', 'maintenance'
union all select 13, '222x', '15'
union all select 14, '12a3', 'inactive'
union all select 14, '456b', 'support'

declare @r table 
(fieldID varchar(10),fieldlabel varchar(50))
insert @r
select '12a3', 'status'
union all select '456b', 'position'
union all select '222x', 'buildingID'

Query

SELECT b.ID,a.fieldID,a.fieldlabel
FROM @r a CROSS APPLY (SELECT DISTINCT ID FROM @t) b
ORDER BY b.ID

Result

ID          fieldID    fieldlabel
----------- ---------- --------------------------------------------------
6           12a3       status
6           456b       position
6           222x       buildingID
10          12a3       status
10          456b       position
10          222x       buildingID
13          12a3       status
13          456b       position
13          222x       buildingID
14          12a3       status
14          456b       position
14          222x       buildingID

PGG_CA
Starting Member

24 Posts

Posted - 2010-02-22 : 15:46:56

Thanks vijayisonly,

Now how do I populate table @t, which already exists as tableA in my database, with the query results?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 15:50:21

You dont need to...those are table variables I used to build some sample data...
In the Query that I have provided, just replace @t and @r with your actual table names...that should do.

EDIT : So..the result is whatever you were looking for?

PGG_CA
Starting Member

24 Posts

Posted - 2010-02-22 : 16:28:48

It's not working for me.

Let me try explaining this again.

I have TableA:

ID fieldID fieldvalue
6 12a3 active
6 456b support
6 222x 10
10 456b maintenance
13 222x 15
14 12a3 inactive
14 456b support
...

Now I want each ID to have 3 fieldIDs (12a3, 456b, and 222x) and fieldvalues. So ID# 6 already has 3, so it's good. Since #10 has one (456b) fieldID only, I want to insert 12a3 and 222x fieldIDs in this table and set the values to 'A'. I should do the same thing for ID#13. #14 has two, so I need one more field ID (222x). After the query, tableA should now look like this:

ID fieldID fieldvalue
6 12a3 active
6 456b support
6 222x 10
10 456b maintenance
10 12a3 A
10 222x A
13 222x 15
13 456b A
13 12a3 A
14 12a3 inactive
14 456b support
14 222x A

...

TableB contains the list of fieldID and fieldLabels:
12a3 status
456b position
222x buildingID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources