SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

g.c.benjamin
Starting Member

7 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
52249 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

7 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

7 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
52249 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000