InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 02:31:13 GMT20http://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxHAve you tried printing the @sql variable before executing?That would give a hint. Maybe the @sql variable holds NULL or something similar happened.Tue, 11 Sep 2012 14:05:10 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxI 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? [code="sql"]ALTER PROCEDURE [dbo].[refresh_view_for_inspection_tables]ASBEGIN 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[/code]Mon, 10 Sep 2012 18:30:42 GMTkinderdesignhttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxGianluca, 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?Thu, 15 Mar 2012 10:57:42 GMTkinderdesignhttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxThe 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?Thu, 15 Mar 2012 10:19:09 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxI tried modifiying/adding the code to what I thought should be changed. Here's what I put in the procedure:[code="sql"]ALTER PROCEDURE refresh_view_for_inspection_tablesASBEGIN 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[/code]But here's what resulted, with nothing added to the view:[code="other"]Running [dbo].[refresh_view_for_unioned_tables].No rows affected.(0 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[refresh_view_for_unioned_tables][/code]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.Thu, 15 Mar 2012 09:55:52 GMTkinderdesignhttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspx[quote][b]kinderdesign (3/15/2012)[/b][hr]Here's the union on how these particular three are related: [code="sql"]SELECT FacilitiesList.NameFROM FacilitiesList INNER JOIN InspectionsList ON InspectionsList.ParentUNID = FacilitiesList.UNID RIGHT OUTER JOIN ViolationList ON InspectionsList.UNID = ViolationList.UNID[/code][/quote]I'm sorry, but I don't understand what this code is supposed to do.Can you clarify please?Thu, 15 Mar 2012 09:23:28 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxThe 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.[code]CREATE PROCEDURE refresh_view_for_unioned_tablesASBEGIN 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[/code]Thu, 15 Mar 2012 09:21:54 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxHere's the union on how these particular three are related: [code="sql"]SELECT FacilitiesList.NameFROM FacilitiesList INNER JOIN InspectionsList ON InspectionsList.ParentUNID = FacilitiesList.UNID RIGHT OUTER JOIN ViolationList ON InspectionsList.UNID = ViolationList.UNID[/code]Thu, 15 Mar 2012 09:09:43 GMTkinderdesignhttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxHow 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?Thu, 15 Mar 2012 09:02:29 GMTkinderdesignhttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspx[quote][b]kinderdesign (3/15/2012)[/b][hr]Thanks Gianluca, but it seems as though this would be pretty complex.[/quote]Not complex at all.5 tables? No problem: just add them inside the IN predicates:[code]... object_id IN ( OBJECT_ID('TableA'), OBJECT_ID('TableB'), OBJECT_ID('TableC'), OBJECT_ID('TableD'), OBJECT_ID('TableE'))[/code]Obviously, the choice is yours.Thu, 15 Mar 2012 08:46:49 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxThanks 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... [code="sql"]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 EventNameFROM BeachInspectionUNION ALLSELECT '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 EventNameFROM BodyArtInspection.......UNION ALLSELECT '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 EventNameFROM WaterInspection[/code][code="sql"]SELECT 'Beach' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSectionFROM BeachViolationUNION ALL......SELECT 'Summer Camp' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSectionFROM SummerCampViolationUNION ALLSELECT 'Water' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSectionFROM WaterViolation[/code][code="sql"]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, DateModifiedInThisFileFROM BeachFacilityUNION 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, DateModifiedInThisFileFROM WaterFacility[/code]....And it continues....Thu, 15 Mar 2012 08:28:29 GMTkinderdesignhttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxThe only way I can think of is a stored procedure with dynamic sql:[code]-- SETUPCREATE 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)[/code][code]ALTER PROCEDURE unionTablesASBEGIN 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[/code]Thu, 15 Mar 2012 04:19:20 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1267182-1292-1.aspxI'm still trying to get the hang of SQL2008.... I've been using SAS and had no problem joining these tabes before there using [code="sql"]SELECT * FROM TABLEA OUTER UNION CORRSELECT * FROM TABLEBOUTER UNION CORRSELECT * FROM TABLEC[/code]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 [code="sql"]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 XUNION ALLSELECT '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 XOUTER UNION CORRSELECT '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[/code]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...Wed, 14 Mar 2012 19:51:40 GMTkinderdesign