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.

  • 4
    The "1st row of each table" is vague. First? Based on what? An IDENTITY column? A specific ORDER BY clause? Or will any particular row be sufficient (as in TOP(1))?
    – Dave Mason
    Jul 25 '14 at 21:49

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.

  • Thank you Frlan for your help :) I'm new to SQL so I'm really sorry if my question was too silly. Thanks once again.
    – Partha
    Jul 25 '14 at 20:38

You could use dynamic SQL

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

SET @sql = 'INSERT INTO TableName ' + NCHAR(13) +
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.


With a CTE to generate some test data like so:

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:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.