March 14, 2012 at 7:51 pm
I'm still trying to get the hang of SQL2008.... I've been using SAS and had no problem joining these tabes before there using
SELECT *
FROM TABLEA
OUTER UNION CORR
SELECT *
FROM TABLEB
OUTER UNION CORR
SELECT *
FROM TABLEC
Yes, I know that most will say you don't want to use * as a select because it's not efficient, but this is a server that doesn't care about efficiency...
We have 14 tables that have varying columns. All have a handful the same (about 20) whereas many are different. They are different because most reports need to treat them differently... We have 2 reports that combine all of their commone fields together and we try to keep that in a view... however, when we go to update in SQL2008 it's a bear to find what is still the same from IT and what has changed. We are not the DBA, I only have query access of the db. The DBA says this can't be done, but I'm hoping someone on here can show otherwise!
Does anyone know how to make this other than
SELECT 'ID' AS SOURCE, X.1,X.2,X.3,X.4,X.5,X.6,X.7,X.8,X.9,X.10,X.11,X.12,X.13,X.14,X.15
FROM TABLEA AS X
UNION ALL
SELECT 'ID' AS SOURCE, X.1,X.2,X.3,X.4,X.5,X.6,X.7,X.8,X.9,X.10,X.11,X.12,X.13,X.14,X.15
FROM TABLEB AS X
OUTER UNION CORR
SELECT 'ID' AS SOURCE, X.1,X.2,X.3,X.4,X.5,X.6,X.7,X.8,X.9,X.10,X.11,X.12,X.13,X.14,X.15
FROM TABLEC AS X
When 13 changes on all of them to 39 (Don't ask why, they can't give me an answer) or they add 68, 69, 70 to all of them, it would be nice not to have to rewrite everything other than the report definintion.
Currently this is done in a view and the report using reporting services just has 'SELECT * FROM GROUPLIST'.... When they add a field we update the view then update the reports that need the new field or chnged field...
March 15, 2012 at 4:19 am
The only way I can think of is a stored procedure with dynamic sql:
-- SETUP
CREATE TABLE TableA (
id int PRIMARY KEY CLUSTERED,
[1] int,
[2] int,
[3] int,
[4] int,
[5] int,
[6] int,
[7] int,
[8] int,
[9] int,
[10] int
)
CREATE TABLE TableB (
id int PRIMARY KEY CLUSTERED,
[1] int,
[3] int,
[4] int,
[5] int,
[8] int,
[9] int,
[10] int,
[11] int,
[12] int
)
CREATE TABLE TableC (
id int PRIMARY KEY CLUSTERED,
[1] int,
[2] int,
[3] int,
[5] int,
[6] int,
[7] int,
[9] int,
[11] int,
[12] int,
[13] int
)
ALTER PROCEDURE unionTables
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SELECT @sql = STUFF((
SELECT ',' + QUOTENAME(name) AS [text()]
FROM sys.columns
WHERE object_id IN (
OBJECT_ID('TableA'),
OBJECT_ID('TableB'),
OBJECT_ID('TableC')
)
GROUP BY name
HAVING COUNT(*) = 3
FOR XML PATH('')
),1,1,SPACE(0))
SELECT @sql = STUFF((
SELECT 'UNION ALL ' + char(10) + 'SELECT ' + @sql + ' FROM ' + name + CHAR(10) AS [text()]
FROM sys.tables
WHERE object_id IN (
OBJECT_ID('TableA'),
OBJECT_ID('TableB'),
OBJECT_ID('TableC')
)
FOR XML PATH('')
),1,10,SPACE(0))
EXEC(@sql)
END
-- Gianluca Sartori
March 15, 2012 at 8:28 am
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....
March 15, 2012 at 8:46 am
kinderdesign (3/15/2012)
Thanks Gianluca, but it seems as though this would be pretty complex.
Not complex at all.
5 tables? No problem: just add them inside the IN predicates:
... object_id IN (
OBJECT_ID('TableA'),
OBJECT_ID('TableB'),
OBJECT_ID('TableC'),
OBJECT_ID('TableD'),
OBJECT_ID('TableE')
)
Obviously, the choice is yours.
-- Gianluca Sartori
March 15, 2012 at 9:02 am
How would this work for combine multiple different tables though? The way I'm reading it, each set would need it's own stored procedure, right? For instance, the 3 examples above are of the full sets that need to be combined. Each combination would be a separate join in SAS currently, as the groups themselves have nothing in common.
I'm also a bit confused, as it looks like it's modifying the union function: Is that only when that function is running or is that a permanant thing?
March 15, 2012 at 9:09 am
Here's the union on how these particular three are related:
SELECT FacilitiesList.Name
FROM FacilitiesList INNER JOIN
InspectionsList ON InspectionsList.ParentUNID = FacilitiesList.UNID RIGHT OUTER JOIN
ViolationList ON InspectionsList.UNID = ViolationList.UNID
March 15, 2012 at 9:21 am
The procedure builds a dynamic SQL statement that UNIONs all the tables selecting all the columns that are present in all the tables.
You can see the statement being run adding "PRINT @sql" right before "EXEC(@sql)".
The statement runs in the moment you call it and does not affect the tables.
If you prefer having a view, you could modify the procedure to update the view definition and invoke it when the tables change.
CREATE PROCEDURE refresh_view_for_unioned_tables
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max);
DECLARE @tables TABLE (
table_id int PRIMARY KEY CLUSTERED
);
INSERT INTO @tables
VALUES (OBJECT_ID('TableA')),
(OBJECT_ID('TableB')),
(OBJECT_ID('TableC')),
(OBJECT_ID('TableD')),
(OBJECT_ID('TableE'));
SELECT @sql = STUFF((
SELECT ',' + QUOTENAME(name) AS [text()]
FROM sys.columns
WHERE object_id IN (
SELECT table_id
FROM @tables
)
GROUP BY name
HAVING COUNT(*) = (SELECT COUNT(*) FROM @tables)
FOR XML PATH('')
),1,1,SPACE(0));
SELECT @sql = STUFF((
SELECT 'UNION ALL ' + char(10) + 'SELECT ' + @sql + ' FROM ' + name + CHAR(10) AS [text()]
FROM sys.tables
WHERE object_id IN (
SELECT table_id
FROM @tables
)
FOR XML PATH('')
),1,10,SPACE(0));
EXEC('IF OBJECT_ID(''unioned_tables'',''V'') IS NOT NULL DROP VIEW unioned_tables;');
SET @sql = 'CREATE VIEW unioned_tables AS ' + @sql;
EXEC(@sql);
END
-- Gianluca Sartori
March 15, 2012 at 9:23 am
kinderdesign (3/15/2012)
Here's the union on how these particular three are related:
SELECT FacilitiesList.NameFROM FacilitiesList INNER JOIN
InspectionsList ON InspectionsList.ParentUNID = FacilitiesList.UNID RIGHT OUTER JOIN
ViolationList ON InspectionsList.UNID = ViolationList.UNID
I'm sorry, but I don't understand what this code is supposed to do.
Can you clarify please?
-- Gianluca Sartori
March 15, 2012 at 9:55 am
I tried modifiying/adding the code to what I thought should be changed. Here's what I put in the procedure:
ALTER PROCEDURE refresh_view_for_inspection_tables
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max);
DECLARE @tables TABLE (
table_id int PRIMARY KEY CLUSTERED
);
INSERT INTO @tables
VALUES (OBJECT_ID('BeachInspection')),
(OBJECT_ID('BodyArtInspection')),
(OBJECT_ID('CampInspection')),
(OBJECT_ID('ChildCareInspection')),
(OBJECT_ID('GeneralInspection')),
(OBJECT_ID('HotelInspection')),
(OBJECT_ID('LaborCampInspection')),
(OBJECT_ID('MobileHomeInspection')),
(OBJECT_ID('PoolInspection')),
(OBJECT_ID('SchoolInspection')),
(OBJECT_ID('SepticRemovalInspection')),
(OBJECT_ID('SummerCampInspection')),
(OBJECT_ID('WaterInspection'));
SELECT @sql = STUFF((
SELECT ',' + QUOTENAME(name) AS [text()]
FROM sys.columns
WHERE object_id IN (
SELECT table_id
FROM @tables
)
GROUP BY name
HAVING COUNT(*) = (SELECT COUNT(*) FROM @tables)
FOR XML PATH('')
),1,1,SPACE(0));
SELECT @sql = STUFF((
SELECT 'UNION ALL ' + char(10) + 'SELECT ' + @sql + ' FROM ' + name + CHAR(10) AS [text()]
FROM sys.tables
WHERE object_id IN (
SELECT table_id
FROM @tables
)
FOR XML PATH('')
),1,10,SPACE(0));
EXEC('IF OBJECT_ID(''InspectionListAutoTest'',''V'') IS NOT NULL DROP VIEW InspectionListAutoTest;');
SET @sql = 'CREATE VIEW InspectionListAutoTest AS ' + @sql;
EXEC(@sql);
END
But here's what resulted, with nothing added to the view:
Running [dbo].[refresh_view_for_unioned_tables].
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[refresh_view_for_unioned_tables]
Question: It looks like this dumps them all together into one big table then is sepparating them out: Am I reading that correctly? If so, that would be even more ideal: It would be great to have one big table that has all of the common fields (merged where column name is identical) and non-common fields, but I didn't think that was possible. Right now we use these views so that the reports aren't having to jump everything together in the report select, using that join that I had noted. That wasn't part of what we are trying to accomplish here, rather what we use on the reports to join these groups of similar tables together with their respective relationships.
I appreciate the help: Please let me know if that explains it better.
March 15, 2012 at 10:19 am
The procedure does not run any query now, but it refreshes the view.
Right click the view and select "Script View AS... --> Create to... --> New Query Editor Window".
Has the view definition changed?
-- Gianluca Sartori
March 15, 2012 at 10:57 am
Gianluca, you are brilliant! The problem was not in your code, rather that all table names were 'inspecitons' and I was missing the 'S'.
Thanks again!
P.S. IS there a way to do the same thing but not remove the duplicate columns and instead add something like {'' AS MissingColumn} in it's place?
September 10, 2012 at 6:30 pm
I have tried and tried and can't seem to get this view to build since upgrading to SS2008R2. If I manually MAKE a view it will drop it (when I remove the --), but I can't get it to update or create a view. Any ideas?
ALTER PROCEDURE [dbo].[refresh_view_for_inspection_tables]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max);
DECLARE @tables TABLE (
table_id int PRIMARY KEY CLUSTERED
);
INSERT INTO @tables
VALUES (OBJECT_ID('BeachInspection')),
(OBJECT_ID('BodyArtInspection')),
(OBJECT_ID('CampInspection')),
(OBJECT_ID('ChildCareInspection')),
(OBJECT_ID('FoodInspection')),
(OBJECT_ID('GeneralInspection')),
(OBJECT_ID('HotelInspection')),
(OBJECT_ID('LaborCampInspection')),
(OBJECT_ID('MobileHomeInspection')),
(OBJECT_ID('PoolInspection')),
(OBJECT_ID('SchoolInspection')),
(OBJECT_ID('SepticRemovalInspection')),
(OBJECT_ID('SummerCampInspection')),
(OBJECT_ID('WaterInspection'));
SELECT @sql = STUFF((
SELECT ',' + QUOTENAME(name) AS [text()]
FROM sys.columns
WHERE object_id IN (
SELECT table_id
FROM @tables
)
GROUP BY name
FOR XML PATH('')
),1,1,SPACE(0));
SELECT @sql = STUFF((
SELECT 'UNION ALL ' + char(10) + 'SELECT ' + @sql + ' FROM ' + name + CHAR(10) AS [text()]
FROM sys.tables
WHERE object_id IN (
SELECT table_id
FROM @tables
)
FOR XML PATH('')
),1,10,SPACE(0));
--EXEC('IF OBJECT_ID(''InspectionListAutoTest'',''V'') IS NOT NULL DROP VIEW InspectionListAutoTest;');
SET @sql = 'CREATE VIEW [InspectionListAutoTest] AS ' + @sql;
EXEC(@sql);
END
September 11, 2012 at 2:05 pm
HAve you tried printing the @sql variable before executing?
That would give a hint. Maybe the @sql variable holds NULL or something similar happened.
-- Gianluca Sartori
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply