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

ahmed salah 3,216 Reputation points
2022-08-24T18:38:13.63+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,051 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 24,351 Reputation points
    2022-08-25T03:39:32.95+00:00

    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

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Viorel 113.7K Reputation points
    2022-08-24T19:32:18.147+00:00

    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  
    

  2. Erland Sommarskog 103.2K Reputation points MVP
    2022-08-24T21:40:10.55+00:00

    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.