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
password
? It would be very helpful if you'd update your question to include sample data and expected results.SELECT REPLACE(NEWID(), '-', '') AS pw FROM sys.all_objects AS ao;