July 11, 2010 at 10:46 am
I have two tables user and user_role. Userid is primary filed into user table and foreign key in user_role table.
User_role has userid, roleid fileds.
There were some rows already into user table and corresponding to these userid fileld into user_role also.
User table example----
Userid loginid fname
1 a john
2 b karry
user_role example-----
user_id roleid
1 10
1 11
2 10
2 15
Later on I inserted some new rows into user tables ,
Now user table is like this----
user table---
Userid loginid fname
1 a john
2 b karry
3 america\c john
4 america\d karry
so i want that this new userid to be inserted into user_role table also with their already existing roleid.
Now the user_role table should like this---
user_id roleid
1 10
1 11
2 10
2 15
3 10
3 11
4 10
4 15
Please send me the query to work in sql server 2005.
July 11, 2010 at 11:11 am
You could use a subquery or CTE to get the roles per fname (including the user_id from the user_role table) and join that back to the user table. Based on that, insert the new values.
Please note that I decided against the option to provided the code snippet since I think you should give it a try based on the description. If you get stuck, post back your current query and what you're struggling with.
July 12, 2010 at 10:22 am
I'm afraid I don't quite follow exactly what you're getting stuck on. Is it how to get the keys back from the rows you just entered? There have been a number of posts and articles on this site discussing how to do that.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
July 13, 2010 at 5:55 am
you need to decide that the new user is going to copy/inherit the roles of a specific user.
for example:
insert into user_role
select
3, --the userid of the new person who will receive/inherit the roles
r.roleid
from [role] r
where r.userid IN(SELECT userid from u WHERE fname = 'john')--he was userid 1 in your example
Lowell
July 9, 2013 at 7:03 am
I realize that this doesn't directly answer your question, but why did you create new user ids for these users? Was there not a way to preserve their existing IDs and update other information about the users?
July 9, 2013 at 7:34 am
Please note: 3 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply