how to make union all in case of some table not exist ?

Question

I work on sql server 2019 i have three tables a and b and c

select id,name from tableA
union
select id,name from tableB
union
select id,name from table C
union
select id,name from TableC

suppose table C not exist
so how to avoid error and execute union without any error

Answer 1

Hi @ahmed salah
Solution 1: Dynamically modify the UNION statement based on whether the table exists.
Check these two alternative methods:

CREATE TABLE tableA(id INT,name VARCHAR(20))  
CREATE TABLE tableB(id INT,name VARCHAR(20))  
CREATE TABLE tableC(id INT,name VARCHAR(20))  
DROP TABLE tableA;  
DROP TABLE tableB;  
DROP TABLE tableC  
--Method 1  
DECLARE @sql NVARCHAR(MAX)  
SELECT @sql = CASE WHEN name = 'tableA' THEN 'select id,name from tableA ' END   
FROM sys.tables WHERE name = 'tableA'  
  
SELECT @sql += CASE WHEN @sql IS NOT NULL AND name = 'tableB' THEN ' union all select id,name from tableB'   
                    WHEN @sql IS NULL AND name = 'tableB' THEN 'select * from tableB' END   
FROM sys.tables WHERE name = 'tableB'  
  
SELECT @sql += CASE WHEN @sql IS NOT NULL AND name = 'tableC' THEN ' union all select id,name from tableC'   
                    WHEN @sql IS NULL AND name = 'tableC' THEN 'select * from tableC' END   
FROM sys.tables WHERE name = 'tableC'  
  
PRINT @sql  
EXEC sp_executesql @sql  
GO  
  
--Method 2   
DECLARE @tableNames VARCHAR(MAX)  
DECLARE @sql VARCHAR(MAX)  
DECLARE @tablelist TABLE(tablename VARCHAR(50))  
  
SET @tableNames='tableA,tableB,tableC'  
SET @sql = 'select name from sys.objects where name in (''' + REPLACE(@tableNames, ',',''',''') + ''')';  
  
INSERT INTO @tablelist EXEC (@sql);  
--creating query with union all   
SELECT @sql = @sql + 'Select id,name from ' + tablename + ' Union All ' From @tablelist;  
SET @sql = LEFT(@sql, len(@sql) - 9);  
PRINT @sql  
EXEC sp_executesql @sql;  

Solution 2: Use a table variable, insert only the rows of the tables that exist in your database. Then select the records of the table variable, and you will have the rows of each existing table.

DECLARE @recordsExistingTables TABLE(id INT,name VARCHAR(20));  
  
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TableA'))  
BEGIN  
   INSERT INTO @recordsExistingTables  SELECT id,name FROM TableA  
END   
  
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TableB'))  
BEGIN  
   INSERT INTO @recordsExistingTables  SELECT id,name FROM TableB  
END   
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TableC'))  
BEGIN  
   INSERT INTO @recordsExistingTables  SELECT id,name FROM TableC  
END   
  
SELECT * FROM @recordsExistingTables;  

Best regards,
LiHong

Answer 2

To avoid the errors after finding that table C does not exist, comment the offending lines, like this:

select id,name from tableA  
union  
select id,name from tableB  
-- union  
-- select id,name from table C  
union  
select id,name from TableC  

Answer 3

You would need to check for existence with object_id and then build a dynamic SQL statement.

Or ask yourself what you are really doing. It sounds to me as if something is broken in the process.