0

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

3
  • Your question is unclear to me. Are you simply wanting to add one additional column to your current query and have that additional column contain a random string of data that would be a password? It would be very helpful if you'd update your question to include sample data and expected results. Nov 30, 2018 at 13:18
  • 1
    How random does it have to be? Could you use something like this? SELECT REPLACE(NEWID(), '-', '') AS pw FROM sys.all_objects AS ao; Nov 30, 2018 at 13:23
  • HI Scott, thank you so much for the quick response. basically about these tables i can not write to them, i can only create tables. Nov 30, 2018 at 14:52

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.