November 23, 2010 at 1:01 pm
I have the following question, not sure if this is possible, I have the following table:
Create Table MyPermissions
(
PerPkey int IDENTITY(1,1) not null,
PerEmployee int not null,
PerA int not null,
PerB int not null,
PerC int not null,
PerD int not null
)
insert into MyPermissions
(PerEmployee,PerA,PerB,PerC,PerD)
values (8159,1,0,0,1), (7189,1,1,1,0)
I want to make take all the permissions that PerEmployee 7189 has and give it to 8159. Is there a way to do this outside of subqueries? Also it is very likely that there will be more fields added to the table so if possible don't want to go in and update the statement all the time.
I looked a solution that Steve Jones had from this discussion
http://www.sqlservercentral.com/Forums/Topic852880-391-1.aspx
its close to what i'm looking for but not sure how to handle the where.
November 23, 2010 at 1:05 pm
First, don't build the table that way.
Make those permissions into rows, not columns.
Once you do that, the whole thing becomes a trivially simple Insert...Select, to copy permissions from one person to another.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 23, 2010 at 1:07 pm
wouldn't i have 2 of the same user in the table then? I want to update the one line with results of the other but they are both in the same table. Only idea i have so far is to run a delete followed by an insert.
November 23, 2010 at 1:09 pm
Okay, you'll need to explain how this data is used, what it models, a bit more, before I can answer anything much on it.
At first glance, it looks like a violation of normal form. But I can't be sure of that without knowing what "PerA", "PerB", et al, mean.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 23, 2010 at 1:20 pm
PerA, PerB etc are permission levels from 0-5. It is possible that the table is not created correctly this was created 8 years ago and is so ingrained in our business that i can't really change it.
thanks for any help you can give.
November 23, 2010 at 1:34 pm
The better way to design the table would have been:
create table dbo.Permissions (
PerEmployee int not null,
PerType char(1) not null,
constraint PK_Permissions primary key (PerEmployee, PerType),
PerLevel tinyint not null,
constraint CK_PerLevel check (PerLevel between 0 and 5));
It's probably too late to fix that now, but if you can refactor that in, work on doing so.
If you need to copy one set of permissions to another within the current table structure, you'll want to look at the Merge command (if you're using SQL 2008, as per the forum this is posted in), or at an "upsert" statement (if it's 2005 or earlier). Either one will do it pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 23, 2010 at 1:37 pm
we are working in 2008, could i unpivot the table to work with it better?
I thought about a merge statement too but was wondering if there was a more dynamic or easier way.
November 23, 2010 at 1:48 pm
Merge will do what you need. One command, and it'll update all four columns to match, or insert if the target row doesn't exist.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply