update fields based on table to table

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)

update fields based on table to table

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2011-03-16 : 12:43:54

I am in brain stroming situation. I thought, lets explain to geeks, So they can help me to resolve this problem.

I have table called Sharepoint_Users in Oracle. (see below format and data). I have access oracle db from linked server.Don't worry about it. Just consider it is sql table.



SharePoint_Users

ID         Status
1           Active
2           InActive
3           Active
4           InActive

another two tables called aspnet_user and aspnet_UsersInRoles in SQL server.



aspnet_users

UserID                                       UserName      

A7DFDDAE-4DB8-476D-9C29-677763406F71          1
D9910E14-9206-4460-88CA-4C39DE620192          2
F188B1DF-03A6-4332-BA89-3B3C6682E9BA          3
728E77E7-693A-4015-92CA-02F0A403C29A          4



asnet_usersInRoles

UserID                                      RoleID

A7DFDDAE-4DB8-476D-9C29-677763406F71        InActive
D9910E14-9206-4460-88CA-4C39DE620192        Active

Now here is the challenge.

When users status changes in SharePoint_Users table from 'Active' to 'InActive' OR 'InActive' to 'Active'. We need to update same users RoleID in asnet_usersInRoles table.

And also I need insert new records those not exists in asnet_usersInRoles table but exists in aspnet_users table.
If user not found in aspnet_users should not insert them into asnet_usersInRoles.
(always users will be the same SharePoint_Users and aspnet_users)

Please help me out write a sp to get it done. I will run job for this sp every 1 hr to update.

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-03-16 : 13:19:17

[code]"When users status changes in SharePoint_Users table from 'Active' to 'InActive' OR 'InActive' to 'Active'.
We need to update same users RoleID in asnet_usersInRoles table."

UPDATE ur
SET ur.RoleID=su.[Status]
FROM SharePoint_Users su
LEFT JOIN aspnet_users au ON au.UserName=su.ID
LEFT JOIN asnet_usersInRoles ur ON ur.UserID=au.UserID
WHERE su.[Status]<>ur.RoleID[/code]

The above code will compare the Status field from the SharepointUsers table to the RoleID in the asnet_usersInRoles table and where they're different, it updates the latter. This doesn't take into account where the ROleID is null, but some minor tweakage could cover this - probably a OR ur.RoleID IS NULL on the end of the code but I haven't tested this.
If you ran this hourly, it would update the fields required.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-03-16 : 13:27:01

[code]"And also I need insert new records those not exists in asnet_usersInRoles table but exists in aspnet_users table.
If user not found in aspnet_users should not insert them into asnet_usersInRoles.
(always users will be the same SharePoint_Users and aspnet_users)"

INSERT INTO asnet_usersInRoles (UserID,RoleID)
SELECT au.UserID,su.[Status]
FROM #SharePoint_Users su
LEFT JOIN #aspnet_users au ON au.UserName=su.ID
LEFT JOIN asnet_usersInRoles ur ON ur.UserID=au.UserID
WHERE ur.UserID IS NULL[/code]

And this code will populate the asnet_usersInRoles table with any users not previously in there.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2011-03-16 : 13:58:25

I forgot to tell something. the asnet_usersInRoles tables "RoleId" is is GUID


asnet_usersInRoles

UserID                                      RoleID

A7DFDDAE-4DB8-476D-9C29-677763406F71        1E36A840-2EBB-44EC-8861-0E3D262AC676 ---> InActive
D9910E14-9206-4460-88CA-4C39DE620192        0B54F223-E0D4-4CFC-84C3-7C98C1BFC6DA --> Active

quote:
Originally posted by theboyholty

"And also I need insert new records those not exists in asnet_usersInRoles table but exists in aspnet_users table.
If user not found in aspnet_users should not insert them into asnet_usersInRoles.
(always users will be the same SharePoint_Users and aspnet_users)"

INSERT INTO asnet_usersInRoles (UserID,RoleID)
SELECT au.UserID,su.[Status]
FROM #SharePoint_Users su
LEFT JOIN #aspnet_users au ON au.UserName=su.ID
LEFT JOIN asnet_usersInRoles ur ON ur.UserID=au.UserID
WHERE ur.UserID IS NULL

And this code will populate the asnet_usersInRoles table with any users not previously in there.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

quote:

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources