Insert rows

  • 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.

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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.'

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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?

  • 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

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply