Sql- Union All a Large Number of Tables

SQL- UNION ALL a large number of tables

using your pattern on table name - i got somewhere with

DECLARE @SQL nvarchar(max);

select @SQL =  COALESCE(@SQL , '') + 'SELECT * FROM [' +  TABLE_NAME + ']  UNION ALL ' 
FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME LIKE '%employeedet%';

SELECT @SQL = LEFT(@SQL, LEN(@SQL) - 11);

print @SQL;

How to UNION ALL tables with tablename satisfying condition in BigQuery?

You can use a wildcard:

select * from `project.dataset.ourtable*`

SQL script to UNION a large number of tables

You can loop over date range in plpgsql function like this:

create or replace function add_map(date_from date, date_to date)
returns void language plpgsql as $$
declare
    day date;
begin
    for day in 
        select generate_series(date_from, date_to, '1 day')
    loop
        execute 'insert into map select * from final_map_'|| 
            to_char(extract(month from day), '09')|| '_' || 
            to_char(extract(day from day), '09');
    end loop;
end; $$;

Calling the function:

-- create table map (....);
select add_map('2012-11-30', '2012-12-02');

is equivalent to:

insert into map select * from final_map_11_30;
insert into map select * from final_map_12_01;
insert into map select * from final_map_12_02;

Performance issues with UNION of large tables

Do the filtering at each step. But first, modify the headquarters table so it has the right type for what you need . . . along with an index:

alter table headquarters add headquarter_int as (cast(headquarter as int));
create index idx_headquarters_int on headquarters(headquarters_int);

SELECT DISTINCT headquarter, country, file
FROM LargeTable5 lt5
WHERE NOT EXISTS (SELECT 1
                  FROM headquarters s
                  WHERE s.headquarter_int = lt5.headquarter and s.deletiondate is not null
                 );

Then, you want an index on LargeTable5(headquarter, country, file).

This should take less than 5 seconds to run. If so, then construct the full query, being sure that the types in the correlated subquery match and that you have the right index on the full table. Use union to remove duplicates between the tables.

SQL Server: What's the limit on number of UNIONs?

From SQL 2008 BOL

"Any number of UNION operators can appear in a Transact-SQL statement"

Also from MSDN "Maximum Capacity Specifications for SQL Server" (2008):

Batch size (1) 65,536 * Network Packet Size

Length of a string containing SQL statements (batch size) (1) 65,536 * Network packet size

(1) Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

To my mind, that means 268,435,456 bytes when using defaults. Please say your query is shorter than that.

Big tables and UNION performances

order by does not help the SQL engine. It just adds additional work. In addition, the union has to remove duplicates.

You might find that this works much, much faster with the appropriate index:

SELECT ts, bid, ask
FROM bal1
UNION ALL
SELECT ts, bid, ask
FROM bal2 b2
WHERE NOT EXISTS (SELECT 1 FROM bal1 b1 WHERE b1.ts = b2.ts and b1.bid = b2.bid and b1.ask = b2.ask)

Of course, this does not remove duplicates within a table. If that is needed then you should add distinct to the two selects.

The index for this is bal1(ts, bid, ask).

You can add an order by ts to the query if you need it. That will take additional time for processing.

SQL Server select queries union all and limit

one problem is that SQL SERVER row_number starts at 1 - you do not get a 0, so you have asked for 0 - 9 which will be 9 rows/

your other problem seems to be that row_number is calculated separately for each part of the UNION (which is logical for it to do so) - try calculating row number in a 2nd CTE

try

    ;WITH Results_CTE1 AS
(

    select  'false' as historico,'' as tabla,a.nombre,a.apellido1,a.apellido2 from persons a where a.eliminado = 'N' and ( idconv = 30 ) 
    union all 
    select  'true' as historico,b.tabla,b.nombre,b.apellido1,b.apellido2 from persons_hist b 
    where b.eliminado = 'N' and ( tabla = '1997' )
),
Results_CTE AS 
(
    SELECT *,ROW_NUMBER() OVER (ORDER BY apellido1 asc ) AS RowNum FROM Results_CTE1
)

SELECT *
FROM Results_CTE
WHERE RowNum BETWEEN 1 AND 10

Union 50+ tables with different number of columns using SQL Server

You can query SQL Server metadata, and from the result dynamically construct a SQL statement. This can be done in any programming language, including T-SQL itself.

Here's a rough example; execute this query, copy/paste the result back into the query window, and execute that.

If the 50 tables have similar names (e.g. all start with Foo), then you can replace the exhaustive table list (WHERE TABLE_NAME IN ('table1', 'table2', 'table3') in my example) by WHERE TABLE_NAME LIKE 'Foo%'.

WITH
    AllTables (TABLE_NAME) AS (
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME IN ('table1', 'table2', 'table3')
    ),
    TablesWithSelectors (TABLE_NAME, COLUMN_NAME, Selector) AS (
        SELECT t.TABLE_NAME, a.COLUMN_NAME, CASE WHEN b.COLUMN_NAME IS NULL THEN 'NULL AS ' ELSE '' END + a.COLUMN_NAME
        FROM AllTables t
        CROSS JOIN (SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM AllTables)) a
        LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS b ON b.TABLE_NAME = t.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME
    ),
    SelectStatements (Sql) AS (
        SELECT
            'SELECT ' +
            STUFF((
                SELECT ', ' + Selector
                FROM TablesWithSelectors
                WHERE TABLE_NAME = r.TABLE_NAME
                FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
            , 1, 2, '') +
            ' FROM ' +
            TABLE_NAME
        FROM TablesWithSelectors r
        GROUP BY TABLE_NAME
    )
SELECT STUFF((
        SELECT ' UNION ALL ' + sql
        FROM SelectStatements
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'), 1, 11, '')

Thanks to:
How to use GROUP BY to concatenate strings in SQL Server?

---

Related Topics