January 9, 2019 at 8:25 am
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
January 9, 2019 at 9:32 am
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
January 10, 2019 at 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?
January 10, 2019 at 4:18 am
zax1990 - Thursday, January 10, 2019 12:47 AMThat 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
January 14, 2019 at 12:30 am
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