Thanks Gianluca, but it seems as though this would be pretty complex. I also forgot to mention that we have 5 sets of theses tables, so it appears at the current time we'll have to keep doing it manually in views until SQL catches up with what we've been doing in SAS for years...

SELECT 'Beach' AS Module, UNID, ParentUNID, SourceDatabaseName, Name, FacilityType, BillingType, EHO, InspectionDate, Type, FollowupInspectionRequired, NextInspection, Comments,

ReviewedBy, NumCritical, NumNonCritical, NumRepeat, '' AS TotalViolations, TotHazardRating, '' AS HazRatingType, '' AS EventName

FROM BeachInspection

UNION ALL

SELECT 'BodyArt' AS Module, UNID, ParentUNID, SourceDatabaseName, Name, FacilityType, BillingType, EHO, InspectionDate, Type, FollowupInspectionRequired, NextInspection, Comments,

ReviewedBy, NumCritical, NumNonCritical, NumRepeat, '' AS TotalViolations, TotHazardRating, '' AS HazRatingType, '' AS EventName

FROM BodyArtInspection

.......

UNION ALL

SELECT 'Water' AS Module, UNID, ParentUNID, SourceDatabaseName, Name, FacilityType, BillingType, EHO, InspectionDate, Type, FollowupInspectionRequired, NextInspection, Comments,

ReviewedBy, NumCritical, NumNonCritical, NumRepeat, '' AS TotalViolations, TotHazardRating, '' AS HazRatingType, '' AS EventName

FROM WaterInspection

SELECT 'Beach' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSection

FROM BeachViolation

UNION ALL

......

SELECT 'Summer Camp' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSection

FROM SummerCampViolation

UNION ALL

SELECT 'Water' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSection

FROM WaterViolation

SELECT 'BeachFacility' AS Module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Name, EHO, Stage, OpenedDate, ClosedDate, FacilityID,

WebHide, TimeOpen, TimeClosed, OperationID, Source, DateCreated, DocumentID, CensusTract, Lot, Section, LegalDescription, GPIN, Block, Subdivision, PropertyID,

Branch, MagDistrict, ProblemFacility, Type, ApplicationDate, Status, PermitDate, InvoiceTo, TerminationDate, IssueDate, ExpirationDate,

PermitNumber, AllYearRound, MonthsOpen, LocalPermit, Violations, LastInspection, LastInspectionTime, TotalViolations, Enforcement, ReleaseDate, NextInspDue,

NextInspection, Freq, LastRoutineInsp, RiskRanking, NextStateInspDue, OwnerSource, OwnerID, OwnersName, ContactLast, ContactFirst, PhysicalBuilding,

PhysicalDirection, PhysicalStreetName, PhysicalStreetType, PhysicalStreetSuffix, PhysicalSuite, PhysicalPostalCode, PhysicalPostalCode AS PhysicalZip, PhysicalCity, PhysicalMunicipality, PhysicalMunicipality AS PhysicalCounty,

PhysicalProvince, PhysicalCountry, Directions, PhysicalAddress, MailingContactLast, MailingContactFirst, MailingBuilding, MailingDirection, MailingStreetName,

MailingStreetType, MailingStreetSuffix, MailingSuite, MailingPostalCode, MailingPostalCode AS MailingZip, MailingCity, MailingMunicipality, MailingMunicipality AS MailingCounty, MailingProvince, MailingCountry, MailingAddress,

PhoneDayArea, PhoneDayNumber, PhoneDayExt, PhoneEveningArea, PhoneEveningNumber, PhoneEveningExt, PhoneCellArea, PhoneCellNumber, PhoneFaxArea,

PhoneFaxNumber, Email, Website, PhoneOther, Comments, FacilityUNID, BillingType, BillingPrimaryID, BillingSecondaryID, MunicipalityCode, DocumentID3,

DateModifiedInThisFile

FROM BeachFacility

UNION ALL

.....

SELECT 'WaterFacility' AS Module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Name, EHO, Stage, OpenedDate, ClosedDate, FacilityID,

WebHide, TimeOpen, TimeClosed, OperationID, Source, DateCreated, DocumentID, CensusTract, Lot, Section, LegalDescription, GPIN, Block, Subdivision, PropertyID,

Branch, MagDistrict, ProblemFacility, Type, ApplicationDate, Status, PermitDate, InvoiceTo, TerminationDate, IssueDate, ExpirationDate,

PermitNumber, AllYearRound, MonthsOpen, LocalPermit, Violations, LastInspection, LastInspectionTime, TotalViolations, Enforcement, ReleaseDate, NextInspDue,

NextInspection, Freq, LastRoutineInsp, RiskRanking, NextStateInspDue, OwnerSource, OwnerID, OwnersName, ContactLast, ContactFirst, PhysicalBuilding,

PhysicalDirection, PhysicalStreetName, PhysicalStreetType, PhysicalStreetSuffix, PhysicalSuite, PhysicalPostalCode, PhysicalPostalCode AS PhysicalZip, PhysicalCity, PhysicalMunicipality, PhysicalMunicipality AS PhysicalCounty,

PhysicalProvince, PhysicalCountry, Directions, PhysicalAddress, MailingContactLast, MailingContactFirst, MailingBuilding, MailingDirection, MailingStreetName,

MailingStreetType, MailingStreetSuffix, MailingSuite, MailingPostalCode, MailingPostalCode AS MailingZip, MailingCity, MailingMunicipality, MailingMunicipality AS MailingCounty, MailingProvince, MailingCountry, MailingAddress,

PhoneDayArea, PhoneDayNumber, PhoneDayExt, PhoneEveningArea, PhoneEveningNumber, PhoneEveningExt, PhoneCellArea, PhoneCellNumber, PhoneFaxArea,

PhoneFaxNumber, Email, Website, PhoneOther, Comments, FacilityUNID, BillingType, BillingPrimaryID, BillingSecondaryID, MunicipalityCode, DocumentID3,

DateModifiedInThisFile

FROM WaterFacility

....And it continues....