How do I select 1st row of each table and insert that data to a new table

Question

i have 5 tables (tab1, tab2, tab3, tab4 and tab5) and all the 5 tables has same table definition. I want to select 1st row of each table and insert those 5 rows in new table(newtab). I was only able to write query that select 1 row from one table and insert that into newtab. How can i select 1st 5 rows from all the table and insert them into newtab in one query.

In this case i have to deal with only 5 tables so its easy using union but when i want to select 1st rows from 100 tables then the query will become but lengthy using UNION.

Answer 1

This is not a complete answer (I'm sorry), but maybe a starting point for further thinking.

For a low number of tables UNION is the best approach IMHO. In special if the list of tables are not changing.

For a bigger number of tables you might can think of using some T-SQL and iterate through a list of tables and building up your query with dynamic SQL -- with this you can select your line(s) into a temporary table maybe.

As I'm not sure where you will get your list of tables from, you could get it from some cursor out of system tables or from some hard coded list maybe.

Answer 2

You could use dynamic SQL

DECLARE @sql nvarchar(max)
SET @sql = STUFF((SELECT ' UNION ALL ' + NCHAR(13) +
                    'SELECT TOP 1 * FROM ' + 
                    QUOTENAME(TABLES.TABLE_SCHEMA) + '.' + 
                    QUOTENAME(TABLES.TABLE_NAME) + NCHAR(13) 
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLES.TABLE_NAME LIKE 'm%'
                FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
            , 1, 12, '') 

SET @sql = 'INSERT INTO TableName ' + NCHAR(13) +
            @sql
PRINT @sql
--EXEC sp_executeSQL @sql

Modify the WHERE TABLES.TABLE_NAME LIKE 'm%' to restrict the list ot the tables you want. Then modify the INSERT INTO TableName to be the table you want.

Personally I would leave the PRINT in place, copy the output and paste into a query window then run inside a transaction. You could however (if you are brave) comment out the print and un-comment the EXEC.

Answer 3

With a CTE to generate some test data like so:

with 
t1 as (select 1 as data union all select 15 as data),
t2 as (select 2 as data union all select 14 as data),
t3 as (select 3 as data union all select 13 as data),
t4 as (select 4 as data union all select 12 as data),
t5 as (select 5 as data union all select 11 as data)

Try this:

select
    t,data
from (
    select 1 as t,data,row_number() over (order by data) as rn from t1
    union all
    select 2 as t,data,row_number() over (order by data) as rn from t2
    union all
    select 3 as t,data,row_number() over (order by data) as rn from t3
    union all
    select 4 as t,data,row_number() over (order by data) as rn from t4
    union all
    select 5 as t,data,row_number() over (order by data) as rn from t5
) T

where rn = 1

which yields:

t           data
----------- -----------
1           1
2           2
3           3
4           4
5           5