Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

What would the SQL2008 equivilent be to outer union corr? Expand / Collapse
Author
Message
Posted Thursday, March 15, 2012 10:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:37 AM
Points: 25, Visits: 66
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?
Post #1267678
Posted Monday, September 10, 2012 6:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:37 AM
Points: 25, Visits: 66
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

Post #1357076
Posted Tuesday, September 11, 2012 2:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1357684
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse