New to MS SQL Server 2012 How do i generate a unique username from user data like initials?

  • Hello, I'm very new to SQL and all things databases. also i feel i need to add i want to lean this and i'm not just looking for a copy paste solution 🙂

    as part of my education, I'm in a project where we need to combine Microsoft SQL server 2012 with some PowerShell to make CSV file with employee data to create users for them in AD.

    I come here for help as the assignment do not specify that there have to be unique usernames but I want to make it with that since that reflects the way it would be useful in a work environment and since its not a part of the assignment the teacher doesn't have time to help me and is also having a hard time solve my problem.

    If there is a better way or a more right way to do this than what I got I'm all ears.
    also, I apologize for some of the danish letters in the columns 

    Right now I'm looking for an "easy" way to generate a username for the employees containing the 2 first letter from first and last name, and if there are more than one of them add +1 at the end.

    What I'm looking for would look something like this (there are ofc a lot more data but I removed that for the simplicity for now:

    FirstName    LastName    Username
    Joe               Frost            JoFr
    Joann           Fran             JoFr1
    Shirlee         Mattack        ShMa

    I have made som attempts but fail in my outputs.
    I know im using a lot of non "Best practice" ways here but i have been working with sql for only 2 weeks by now
    SQL query code:

    use master
    go

    drop database Company
    go

    Create database Company
    go

    use company
    go

    CREATE TABLE ansatte
        (
        FNavn varchar (50) NOT NULL,
        ENavn varchar (50) NOT NULL,
        email varchar (50) NOT NULL,
        [adresse] varchar (50) NOT NULL,
        postnr varchar (50) NOT NULL,
        Tlf varchar (50) NOT NULL,
        [password] varchar (50) NOT NULL,
        Afdeling varchar (50) NOT NULL,
        Tidel varchar (50) NOT NULL
        )
        go

    BULK INSERT ansatte
    FROM 'C:\sql\Nye_Brugere.csv'
    WITH
    (
                firstrow = 2,
                codepage = 'ACP',
                datafiletype = 'char',
                fieldterminator = ';',
                rowterminator = '\n'
        )
    go

    Alter table ansatte
        
        ADD
            ID varchar (10) ,
            PersonId varchar (50)
        go

    UPDATE ansatte SET ID = CONCAT(left(FNavn,2),left(ENavn,2));
    go

    with Usernames(PersonId, Username,ID) as
    (
      select PersonId,
        (left(FNavn, 2) + left(ENavn, 2)) as Username, ID
      from ansatte
    ),
    NumberedUserNames(PersonId, Username, Number) as
    (
      select PersonId,
        Username,
        ROW_NUMBER() OVER (PARTITION BY Username ORDER BY Username) as Number
      from Usernames
    ),
    UniqueUsernames(PersonId, UniqueUsername) AS
    (
      select PersonId,
        case when Number = 1 then UserName
               else UserName + CAST(Number-1 as VarChar(20))
        End as UniqueUsername
      from NumberedUserNames
    )

    select UniqueUsername
        into [username]
        from UniqueUsernames;

    go

    SELECT [ansatte].FNavn, [ansatte].ENavn, [ansatte].email, [ansatte].[adresse], [ansatte].postnr, [ansatte].Tlf, [ansatte].[password], [ansatte].Afdeling, [ansatte].Tidel, UniqueUsername, [ansatte].ID
        FROM [ansatte], [username]
        where [ansatte].ID = left([username].[UniqueUsername],4)
        go

    This is the output from my code
     

    Mock data used for users

    FNavn;MNavn;ENavn;email;[adresse] ;postnr ;Tlf ;[password] ;Afdeling ;Tidel
    Joe;Frost;asemper0@wisc.edu;49496 Calypso Avenue;5664;96156483;Legoland28;Direktion;Direktør
    Garrott;Iacavone;ebloodworthe1@businessinsider.com;2693 Del Sol Place;8293;92974055;Legoland28;Direktion;Sekretær
    Shirlee;Mattack;ddillaway2@google.ca;95160 Farmco Place;8408;89732661;Legoland28;Salg;SalgsChef
    Walter;Jackson;sjerromes3@jigsy.com;56659 Haas Hill;4154;43947147;Legoland28;Salg;Afdelingsleder
    Walker;Jacquest;dbraban4@mlb.com;7 Southridge Parkway;5216;55169889;Legoland28;Salg;Sælger
    Joann;Fran;cdeakins5@blinklist.com;74 Mcguire Trail;3820;40849799;Legoland28;Salg;Sælger
    Ingeberg;Busby;kashford6@tmall.com;2524 Thackeray Avenue;112;20791196;Legoland28;Salg;Sælger
    Jacquelin;Riddich;aquartermaine7@shinystat.com;015 Melody Junction;8480;36210199;Legoland28;Salg;Kontorelev
    Cedric;Balnaves;epaeckmeyer8@sakura.ne.jp;64 Lerdahl Place;7434;26126254;Legoland28;Indkøb;Indkøbschef
    Rozalie;Philipet;lcurnok9@jigsy.com;97 Shoshone Street;7037;26777601;Legoland28;Indkøb;Indkøber
    Berton;Carvell;tcamossoa@scientificamerican.com;2971 Orin Alley;9573;64976257;Legoland28;Administration;Chef
    Garey;Harper;awillettb@rakuten.co.jp;5 Express Avenue;4427;30754497;Legoland28;Administration;Kontorassistent
    Holly;Asif;kellwoodc@umn.edu;53450 Johnson Park;8227;22223422;Legoland28;Administration;Kontorelev
    Corabelle;Goskar;gwyethd@godaddy.com;733 Armistice Trail;4960;49691717;Legoland28;HR;HR-Chef
    Ginelle;Whittock;cmaidlowe@examiner.com;201 Gateway Trail;7796;80611971;Legoland28;HR;Kontorassistent
    Vinny;Alessandrucci;tbummfreyf@tripod.com;3 Holy Cross Drive;3272;34628938;Legoland28;HR;Informationsmedarbejder
    Boothe;Bobasch;astanyardg@pbs.org;5 Fieldstone Place;8898;83521374;Legoland28;Økonomi;Økonomichef
    Gene;Meredyth;mdiggarh@tinyurl.com;1 Lighthouse Bay Place;2234;46710717;Legoland28;Økonomi;Bogholder
    Cindie;Haddinton;siacobuccii@whitehouse.gov;578 Erie Circle;6068;75544654;Legoland28;Økonomi;Kasserer
    Norris;Crosson;jdowmanj@businesswire.com;34 American Avenue;4779;75782837;Legoland28;IT;IT-Chef
    Madelena;Ricarde;aedwickek@illinois.edu;4802 Village Park;6861;93574323;Legoland28;IT;NetværksAdmin
    Garek;Hartshorn;parchanbaultl@bigcartel.com;3735 Stuart Junction;7503;28642516;Legoland28;IT;Lærling

  • Two different people can have the same first and last names. So you need to give these different UserNames, Using email as the unique identifier is often used.
    DROP TABLE ansatte
    GO
    CREATE TABLE ansatte
    (
        FNavn varchar (50) NOT NULL,
        ENavn varchar (50) NOT NULL,
        email varchar (200) NOT NULL
    )
    GO
    INSERT INTO ansatte
    VALUES
    ('Joe','Frost','JoeFrost@email.com'),
    ('Joe','Frost','JoanFrost@gmail.com'), /* Different person to above */
    ('Joann','Fran','JoannFran@email.com'),
    ('Shirlee','Mattack','ShirleeMattack@email.com')
    GO

    ;WITH CTE AS
    (
       SELECT FNavn,
              ENavn,
              email,
              LEFT(FNavn,2) + LEFT(ENavn,2) UserName,
              ROW_NUMBER() OVER (PARTITION BY LEFT(FNavn,2) + LEFT(ENavn,2) ORDER BY FNavn,ENavn) -1 RowNum
        FROM ansatte
    )
    SELECT X.FNavn, X.ENavn, X.email,
           CASE WHEN X.RowNum = 0 THEN UserName
                 ELSE UserName + CONVERT(varchar, X.RowNum)
           END UserNAme
      FROM CTE X

  • That seems so simple compared to what I'm doing!

    one question tho.
    I'm trying to add in all the last data I got, like address, phone number, department and AD OU's and group's
    which I got working with a bulk insert instead of  "INSERT INTO ansatte" but is there a way at the end to add the username to the ansatte table or to make a new table containing all the info from ansatte and the usernames?

  • zax1990 - Thursday, January 10, 2019 12:47 AM

    That seems so simple compared to what I'm doing!

    one question tho.
    I'm trying to add in all the last data I got, like address, phone number, department and AD OU's and group's
    which I got working with a bulk insert instead of  "INSERT INTO ansatte" but is there a way at the end to add the username to the ansatte table or to make a new table containing all the info from ansatte and the usernames?

    To use ROW_NUMBER the data has to already be in the table.
    So you would need to process the username after you have imported the table.
    If you were to add a username column to the table you should be able to do a post process update the ansatte table like this:
    ;WITH CTE AS
    (
     SELECT Username,
            LEFT(FNavn,2) + LEFT(ENavn,2) GeneratedUserName,
            ROW_NUMBER() OVER (PARTITION BY LEFT(FNavn,2) + LEFT(ENavn,2) ORDER BY FNavn,ENavn) -1 RowNum
       FROM dbo.ansatte
    )
    UPDATE X
       SET X.Username = CASE WHEN X.RowNum = 0 THEN X.GeneratedUserName
                             ELSE GeneratedUserName + CONVERT(varchar, X.RowNum)
                        END

      FROM CTE X

  • Hi Jonathan AC Roberts

    Thx for the help all works perfectly now!

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

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