Fill in missing records - SQL Server Forums

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

Transact-SQL (2008)

Fill in missing records

Author

Topic

g.c.benjamin
Starting Member

10 Posts

Posted - 2012-11-14 : 23:53:24

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

1974 Posts

Posted - 2012-11-15 : 02:16:29

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-15 : 10:30:39

[code]
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)
[/code]

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

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-15 : 10:39:08

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

g.c.benjamin
Starting Member

10 Posts

Posted - 2012-11-15 : 20:00:24

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?

g.c.benjamin
Starting Member

10 Posts

Posted - 2012-11-15 : 20:37:35

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-15 : 22:14:33

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources