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

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  January 9, 2019 at 9:32 am

  #2018433

  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

  

• zax1990

  SSC Veteran

  Points: 212

  More actions

  January 10, 2019 at 12:47 am

  #2018479

  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?

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  January 10, 2019 at 4:18 am

  #2018501

  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

• zax1990

  SSC Veteran

  Points: 212

  More actions

  January 14, 2019 at 12:30 am

  #2018804

  Hi Jonathan AC Roberts

  Thx for the help all works perfectly now!