Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Fill in missing records
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

g.c.benjamin
Starting Member

10 Posts

Posted - 11/14/2012 :  23:53:24  Show Profile  Reply with Quote
Through a bug I created, users on my site weren't being automatically added to the sites default roles. I fixed the problem but now there are users who don't belong to all the roles they should. I want to right a script that will find which users this affected and add them to the right roles.

The 3 tables I think I need to use are
ROLES which contains a WebsiteId and RoleId and AutoAssign.
USERROLES which contains a UserId and a RoleId
USERWEBSITE which contains a WebsiteId and a UserId

So what I'm trying to do but failing at miserably, is to add a record to the USERROLES table where the user belongs to website but the role that should have been auto assigned wasn't.

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/15/2012 :  02:16:29  Show Profile  Visit chadmat's Homepage  Reply with Quote
I'll take a stab:

CREATE TABLE #Roles (WebsiteID int, RoleID int, AutoAssign bit)
CREATE TABLE #UserRoles (UserID int, RoleID int)
CREATE TABLE #UserWebsite(WebsiteID int, UserID int)

INSERT INTO #Roles Values (1, 1, 1)
INSERT INTO #Roles Values (1, 2, 0)
INSERT INTO #Roles Values (2, 3, 1)
INSERT INTO #Roles Values (2, 4, 1)
INSERT INTO #Roles Values (2, 5, 0)

INSERT INTO #UserWebsite Values(1, 1)
INSERT INTO #UserWebsite Values(2, 1)
INSERT INTO #UserWebsite Values(1, 2)
INSERT INTO #UserWebsite Values(1, 3)

INSERT INTO #UserRoles Values(1, 1)
INSERT INTO #UserRoles Values(1, 3)
INSERT INTO #UserRoles Values(1, 4)
--Commenting this one out because I want it to be missing
--INSERT INTO #UserRoles Values(2, 1)
INSERT INTO #UserRoles Values(3, 1)
INSERT INTO #UserRoles Values(3, 2)


--Select Missing Roles (Expecting 1 row):
SELECT uw.UserID, r.RoleID FROM #Roles r
JOIN #UserWebsite uw ON r.WebsiteID = uw.WebsiteID
LEFT JOIN #UserRoles ur ON ur.RoleID = r.RoleID AND ur.UserID = uw.UserID
WHERE r.AutoAssign = 1
AND ur.RoleID IS NULL


INSERT INTO #UserRoles
SELECT uw.UserID, r.RoleID FROM #Roles r
JOIN #UserWebsite uw ON r.WebsiteID = uw.WebsiteID
LEFT JOIN #UserRoles ur ON ur.RoleID = r.RoleID AND ur.UserID = uw.UserID
WHERE r.AutoAssign = 1
AND ur.RoleID IS NULL


--Select Missing Roles (Expecting 0 Rows):
SELECT uw.UserID, r.RoleID FROM #Roles r
JOIN #UserWebsite uw ON r.WebsiteID = uw.WebsiteID
LEFT JOIN #UserRoles ur ON ur.RoleID = r.RoleID AND ur.UserID = uw.UserID
WHERE r.AutoAssign = 1
AND ur.RoleID IS NULL



DROP TABLE #Roles
DROP TABLE #UserRoles
DROP TABLE #UserWebsite
GO


-Chad
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/15/2012 :  10:30:39  Show Profile  Reply with Quote

INSERT INTO UserRoles
SELECT uw.UserId,r.RoleId
FROM USERWEBSITE uw
INNER JOIN ROLES r
ON r.WebsiteId = uw.WebsiteId
WHERE NOT EXISTS (SELECT 1
FROM USERROLES
WHERE UserId = uw.UserId
AND RoleId = r.RoleId)


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

Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/15/2012 :  10:39:08  Show Profile  Visit chadmat's Homepage  Reply with Quote
quote:
Originally posted by visakh16


INSERT INTO UserRoles
SELECT uw.UserId,r.RoleId
FROM USERWEBSITE uw
INNER JOIN ROLES r
ON r.WebsiteId = uw.WebsiteId
WHERE NOT EXISTS (SELECT 1
FROM USERROLES
WHERE UserId = uw.UserId
AND RoleId = r.RoleId)


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





I tried that first, but it wasn't working for some reason (Maybe I typed something wrong), so I went with the outer join, which works.

-Chad
Go to Top of Page

g.c.benjamin
Starting Member

10 Posts

Posted - 11/15/2012 :  20:00:24  Show Profile  Reply with Quote
Thanks, much better than my solution! One thing though, running the following works when inserting into the UserRoles table:

INSERT INTO UserRoles (UserID, RoleID)
VALUES (@UserID, @RoleID)

But running your solution I get this error:

Column name or number of supplied values does not match table definition.

There are other columns in the UserRoles table, but these are all nullable. Do I have to specify the nullable values in the query?
Go to Top of Page

g.c.benjamin
Starting Member

10 Posts

Posted - 11/15/2012 :  20:37:35  Show Profile  Reply with Quote
No need to answer last question, I just supplied the null columns in the query and worked. Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/15/2012 :  22:14:33  Show Profile  Reply with Quote
quote:
Originally posted by g.c.benjamin

No need to answer last question, I just supplied the null columns in the query and worked. Thanks!


it shouldnt be a problem so far as you provide column list with insert

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000