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

Developer technologies | Transact-SQL
SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 31,571 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 122.6K 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 121.9K Reputation points MVP Volunteer Moderator
    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.