December 12, 2016 at 11:09 pm
Hi Folks,
I am new to SQL and i have table which has column FULLNAME (Last name, firstname) & USER EMAIL (firstname.lastname@corp.com) and i m looking to replace the value of Fullname like user0001 and User email like user0001@corp.com.
In the table total user count is like 1892 with duplicate as well.
Need expert help.
Regards,
Pankaj
December 12, 2016 at 11:37 pm
pankaj 43245 (12/12/2016)
Hi Folks,I am new to SQL and i have table which has column FULLNAME (Last name, firstname) & USER EMAIL (firstname.lastname@corp.com) and i m looking to replace the value of Fullname like user0001 and User email like user0001@corp.com.
In the table total user count is like 1892 with duplicate as well.
Need expert help.
Regards,
Pankaj
Hi and welcome to the forum.
Can you please post the DDL (create table) script, some sample data as an insert statement and the expected results?
😎
December 13, 2016 at 2:22 am
When you say you want to replace their name with a user, do you therefore have a username specific users should therefore be assigned? If not, how do you determine who is user 00001, and who is user009999?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 13, 2016 at 2:50 am
existing table looks like this
FULLNAME USER_EMAIL Hostname
lastname, firstnamefirstname.lastname@CORP.COM test.corp.com
and outcome result is like
FULLNAMEUSER_EMAIL hostname
user0001user0001@CORP.COM test.corp.com
user9999user9999@corp.com test100.corp.com
i can replace them with update but to do this for 1892 user on 5 different table is time consuming task.
December 13, 2016 at 2:52 am
Hi Thom,
Yes i have specific user to be assign to existing user which will start in from user0001 to user2000.
December 13, 2016 at 2:55 am
pankaj 43245 (12/13/2016)
Yes i have specific user to be assign to existing user which will start in from user0001 to user2000.
So where is the data held? If you have a User, John Smith, and he needs to be user001245 how do i know that John Smith needs to become user001245?
For DDL and Sample data, have a look at the link in my signature.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 13, 2016 at 3:17 am
pankaj 43245 (12/13/2016)
i can replace them with update but to do this for 1892 user on 5 different table is time consuming task.
Create and excel with source and target values
Import this into a temporary table
Write select joining with all four tables and make sure you are happy with the results
Take a backup if you wish
Change the select join to update (inside transaction)
Commit if happy with the result
drop temp table
December 13, 2016 at 3:48 am
This technically works, but like i said, how do you know what user needs what user ID? What is your ordering? i have no idea what your DDL looks like. Should you be storing your existing usernames off somewhere else (i find it hard to believe you want anonymity)?
CREATE TABLE #Users (ID INT IDENTITY(1,1),
Fullname VARCHAR(100),
EmailAddress VARCHAR(200));
INSERT INTO #Users (Fullname,EmailAddress)
VALUES ('Smith, John', 'jmithsmith@test.com'),
('Doe, Jane', 'Janedoe@test.cmom'),
('Bloggs, Steve', 'stevebloggs@test.com');
SELECT *
FROM #Users;
SELECT *, ROW_NUMBER() OVER (ORDER BY Fullname) AS UserID
INTO #Temp
FROM #Users U;
UPDATE #Users
SET Fullname = 'User' + RIGHT('0000' + CAST(UserID AS VARCHAR(4)),4),
EmailAddress = 'User' + RIGHT('0000' + CAST(UserID AS VARCHAR(4)),4) + '@test.com'
FROM #Temp T
WHERE T.ID = #Users.ID
SELECT *
FROM #Users;
DROP TABLE #Temp;
DROP TABLE #Users;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply