Need help with a select, insert

  • GOAL: insert into tblcompanyassignments from tbluserassignments. Only attempt the insert for userid that exist in both tbluserassignments and tblexistingusers. For existing users, further filter down the insert attempt for only companies that exist. Final requirement, don't attempt to insert if there is already an existing user, company combination

    tbluserassignments

    username

    companyname

    record 1: userA, companyA

    record 2: userA, companyB

    record 3: userB, companyB

    record 4: userB, companyC

    record 5: userC, companyA

    record 5: userC, companyB

    tblexistingusers

    userid

    record 1: userA

    record 2: userB

    tblexistingcompanies

    companyid

    record 1: companyA

    record 2: companyB

    tblcompanyassignments

    userid

    companyid

    record 1: userA, companyA

    record 2: userB, companyB

  • Sounds suspiciously like homework...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • I agree it looks like homework.

    But the funny part is: the expected result is wrong based on the given requirement (at least form my point of view)... :w00t:

    @daglugub: what have you tried so far and where did you get stuck?

    We'll be glad to help you better understand SQL Server and/or T-SQL programming. But most of us will have a hard time to simply do your homework.

    Providing help and assitance for SQL Server is our passion and "free of charge". But to do some work for you is called a "job", usually being paid for...



    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]

  • What I can't forgive is the lack of effort to even disguise the question lol. It's like here's my assignment for Monday morning, I'm off to the pub with my mates, see you when you've got the answer.

    :w00t: :w00t: :w00t: :w00t:

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • I took the time to create that scenario because I am asking for help so I wanted to be specific as possible. I guess that backfired huh.

    Here is what I have come up with so far.

    select LTRIM(RTRIM(SUBSTRING (Assignment, PATINDEX('% %',Assignment), 100))) AS LastName, *

    into dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP

    from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE

    select userid

    into #existingusers

    from [devdb4\dev].mnetreportup.dbo.tblusers

    where email in (select email from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)

    OR Lname in (select LastName from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)

    select *

    from #existingusers

    select distinct companyid

    into #existingcompanies

    from [devdb4\dev].mnetreportup.dbo.tblcompanies

    where [name] in (select [Customer Name]from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)

    select *

    from #existingcompanies

    So from the imported data stage table I have record sets for which users are in the system and which companies are in the system.

    Where I am stuck(because I am a novice) is I don't know how to write an insert from the stage table into the usercompany table while using the temp tables I have built up.

    For the record, I am not looking for code to be provided, more so some fundamental education

  • daglugub 60457 (2/25/2013)


    I took the time to create that scenario because I am asking for help so I wanted to be specific as possible. I guess that backfired huh.

    Here is what I have come up with so far.

    select LTRIM(RTRIM(SUBSTRING (Assignment, PATINDEX('% %',Assignment), 100))) AS LastName, *

    into dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP

    from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE

    select userid

    into #existingusers

    from [devdb4\dev].mnetreportup.dbo.tblusers

    where email in (select email from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)

    OR Lname in (select LastName from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)

    select *

    from #existingusers

    select distinct companyid

    into #existingcompanies

    from [devdb4\dev].mnetreportup.dbo.tblcompanies

    where [name] in (select [Customer Name]from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)

    select *

    from #existingcompanies

    So from the imported data stage table I have record sets for which users are in the system and which companies are in the system.

    Where I am stuck(because I am a novice) is I don't know how to write an insert from the stage table into the usercompany table while using the temp tables I have built up.

    For the record, I am not looking for code to be provided, more so some fundamental education

    Sounds like you should look into the MERGE statement. It allows you to insert, update, or delete rows in a target table based on whether they match rows in the source table and satisfy any additional conditions you specify.

    Jason Wolfkill

  • wolfkillj (2/26/2013)


    daglugub 60457 (2/25/2013)


    I took the time to create that scenario because I am asking for help so I wanted to be specific as possible. I guess that backfired huh.

    Here is what I have come up with so far.

    select LTRIM(RTRIM(SUBSTRING (Assignment, PATINDEX('% %',Assignment), 100))) AS LastName, *

    into dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP

    from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE

    select userid

    into #existingusers

    from [devdb4\dev].mnetreportup.dbo.tblusers

    where email in (select email from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)

    OR Lname in (select LastName from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)

    select *

    from #existingusers

    select distinct companyid

    into #existingcompanies

    from [devdb4\dev].mnetreportup.dbo.tblcompanies

    where [name] in (select [Customer Name]from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)

    select *

    from #existingcompanies

    So from the imported data stage table I have record sets for which users are in the system and which companies are in the system.

    Where I am stuck(because I am a novice) is I don't know how to write an insert from the stage table into the usercompany table while using the temp tables I have built up.

    For the record, I am not looking for code to be provided, more so some fundamental education

    Sounds like you should look into the MERGE statement. It allows you to insert, update, or delete rows in a target table based on whether they match rows in the source table and satisfy any additional conditions you specify.

    +1 to Wolfy for suggesting MERGE. At the very least, the suggested solution should be enclosed in a TRANSACTION with error handling (TRY/CATCH blocks).


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Daglugub, how about this?

    --DDL to set up environment

    create table userassignments (id int, userid int, companyname varchar(50));

    insert into userassignments

    values

    (1, '1', 'companyA'),

    (2, '1', 'companyB'),

    (3, '2', 'companyB'),

    (4, '2', 'companyC'),

    (5, '3', 'companyA'),

    (6, '3', 'companyB');

    create table existingusers (userid int, username varchar(10));

    insert into existingusers values

    (1, 'userA'),

    (2, 'userB');

    create table existingcompanies (companyid int, companyname varchar(50));

    insert into existingcompanies values

    (1, 'companyA'),

    (2, 'companyB'),

    (3, 'companyC');

    create table companyassignments (username varchar(10), companyname varchar(50));

    insert into companyassignments values

    ('userA', 'companyA'),

    ('userB', 'companyB');

    --DDL for Merge/Update statement

    MERGE companyassignments T

    USING

    (SELECT ua.userid, eu.username, ua.companyname

    FROM userassignments ua

    JOIN existingusers eu on

    eu.userid = ua.id

    where companyname in (

    SELECT companyname

    FROM existingcompanies)) as S

    on t.username = s.username

    and t.companyname = s.companyname

    WHEN NOT MATCHED BY Target

    THEN INSERT

    (username, companyname)

    VALUES

    (s.username, s.companyname);

    --Quote me

  • polkadot (2/26/2013)


    ...

    --DDL for Merge/Update statement

    MERGE companyassignments T

    USING

    (SELECT ua.userid, eu.username, ua.companyname

    FROM userassignments ua

    JOIN existingusers eu on

    eu.userid = ua.id

    where companyname in (

    SELECT companyname

    FROM existingcompanies)) as S

    on t.username = s.username

    and t.companyname = s.companyname

    WHEN NOT MATCHED BY Target

    THEN INSERT

    (username, companyname)

    VALUES

    (s.username, s.companyname);

    That would be DML.:-)


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • ooh man, you got my back. Thanks. would'a goofed with that in my next interview 🙂

    --Quote me

  • thanks a lot Polka Dot for the DDL and DML. This is definitely the right kind of solution for me. Unfortunately this code fell a little short. It only inserted 2 rows.

    User A Company A

    User B Company B

    But this is a great starting point for me, and I can research and test from here.

    Thanks again

  • polkadot (2/27/2013)


    ooh man, you got my back. Thanks. would'a goofed with that in my next interview 🙂

    🙂 I'm usually looking for ways to disseminate my superiority...

  • I figured it out

    eu.userid = ua.id

    needed to be

    eu.userid = ua.userid

    I am going to start using Merge for a few different tasks I have.

  • Hi Daglugub, sorry I didn't see this sooner. Need to get notifications sent to a more often used email inbox, which I will do right way. But, I am glad you figured it out. PS. NICE WORK

    --Quote me

Viewing 14 posts - 1 through 13 (of 13 total)

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