July 9, 2010 at 9:40 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 ,
Please note:Only loginid will be the different and other filelds will remain same.
for these new rows loginid will be always like this domain\id
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.
because userid 3 and 4 are new entries into user table for userid 1 and 2.
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 13, 2010 at 1:26 am
What do you mean by already existing roleid? I believe the user-role association is created in user_role table. So, how can users be assigned roleids before entries are made in user_role? Please explain this.
Assuming that the roleid is 'hard-coded', please find the query below. Here, I assume you have a datetime column called AddedDate in User table.
insert into user_role
select user_id, 10 from where addeddate > '9 Jul 2010'
If you want to add these users in all the roles available in the role table, do a cross join as follows.
insert into user_role
select u.user_id, r.roleid
from u
cross join role r
where u.addeddate > '9 Jul 2010'
You may add a nonclustered index on the datetime field.
- arjun
https://sqlroadie.com/
July 13, 2010 at 5:43 am
Dont cross post , its simply wastage of time
reply on this http://www.sqlservercentral.com/Forums/Topic950491-145-1.aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply