Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert rows Expand / Collapse
Author
Message
Posted Sunday, July 11, 2010 10:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 30, 2013 1:54 AM
Points: 39, Visits: 118
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.
Post #950491
Posted Sunday, July 11, 2010 11:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 7,161, Visits: 13,229
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.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #950495
Posted Monday, July 12, 2010 10:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:51 PM
Points: 880, Visits: 607
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.'
Post #950890
Posted Tuesday, July 13, 2010 5:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
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 [user] u WHERE fname = 'john')--he was userid 1 in your example




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #951340
Posted Tuesday, July 9, 2013 7:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 9:15 AM
Points: 21, Visits: 9
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?
Post #1471594
Posted Tuesday, July 9, 2013 7:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
Please note: 3 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1471613
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse