SQL, creating a new table with auto generated passords from other tables

Question

i have this sql below which i have retrieved from our student database. It retreives all that i need, but i would like to create a new table which includes all the tables plus one column with auto generated passwords.

Is this possible?

SELECT
    CurrentPupil.SchoolID AS 'SchoolID',
    CurrentPupil.PupilID AS 'PupilID',
    PupilPersonalDetails.Surname AS 'Surname',
    PupilPersonalDetails.Forename AS 'First_Name',
    PupilPersonalDetailsGenderLookupDetails.Description AS 'Gender',
    ContactMaster.LastName AS 'Contact_Last_Name',
    ContactMaster.FirstName AS 'Contact_First_Name',
    ContactMasterGenderLookupDetails.Description AS 'Contact_Gender',
    ContactMaster.LastName2 AS 'Contact_Last_Name_2',
    ContactMaster.FirstName2 AS 'Contact_First_Name_2',
    ContactMasterGender2LookupDetails.Description AS 'Contact_Gender_2',
    ContactEmailInformation.EmailID AS 'Contact_Email_Address',
    PupilCurrentSchoolYearGroupLookupDetails.Description AS 'Year_Group',
    PupilCurrentSchoolFormSchoolLookupDetails.Description AS 'Form',
    ContactMaster.ContactID
FROM
    win2k12engage.EngageSpratton.dbo.CurrentPupil
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilPersonalDetails AS PupilPersonalDetails ON CurrentPupil.SchoolID = PupilPersonalDetails.SchoolID
AND CurrentPupil.PupilID = PupilPersonalDetails.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS PupilPersonalDetailsGenderLookupDetails ON PupilPersonalDetailsGenderLookupDetails.LookupDetailsID = PupilPersonalDetails.Gender
AND PupilPersonalDetailsGenderLookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilContacts AS PupilContacts ON CurrentPupil.SchoolID = PupilContacts.SchoolID
AND CurrentPupil.PupilID = PupilContacts.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.ContactMaster AS ContactMaster ON PupilContacts.ContactID = ContactMaster.ContactID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.ContactEmailInformation AS ContactEmailInformation ON ContactMaster.ContactID = ContactEmailInformation.ContactID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS ContactMasterGenderLookupDetails ON ContactMasterGenderLookupDetails.LookupDetailsID = ContactMaster.Gender
AND ContactMasterGenderLookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS ContactMasterGender2LookupDetails ON ContactMasterGender2LookupDetails.LookupDetailsID = ContactMaster.Gender2
AND ContactMasterGender2LookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilCurrentSchool AS PupilCurrentSchool ON CurrentPupil.SchoolID = PupilCurrentSchool.SchoolID
AND CurrentPupil.PupilID = PupilCurrentSchool.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS PupilCurrentSchoolYearGroupLookupDetails ON PupilCurrentSchoolYearGroupLookupDetails.LookupDetailsID = PupilCurrentSchool.YearGroup
AND PupilCurrentSchoolYearGroupLookupDetails.LookupID = '3004'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.SchoolLookupDetails AS PupilCurrentSchoolFormSchoolLookupDetails ON PupilCurrentSchoolFormSchoolLookupDetails.LookupDetailsID = PupilCurrentSchool.Form
AND PupilCurrentSchoolFormSchoolLookupDetails.LookupID = '1002'
WHERE
    ('%wc%' = '%wc%')

*************************************UPDATE*************************** Hi Guys back to my desk. I am adding one additional column to the current query and have that additional column contain a random string of data that would be a password...also the tables / columns above are from a database that i cannot write to but only read, so i use the win2k12engage.EngageSpratton.dbo.fieldname to connect to my other database Eletter.dbo.newtable where i want to have all the fields above plus one more column with the password.

Cheers

Many thanks in advance Vitor

Answer 1

I don't have a Database to test this on, but Potentially, you could use DEFAULT, RAND() and REPLACE() to do this.

CREATE TABLE Users( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Password varchar(255) DEFAULT REPLACE(FLOOR(RAND()*(987654321-123456789+1))+123456789,'1','!') );

Just keep adding more REPLACE to make the Password a bit more Complex with each.