Click here to Skip to main content
15,885,546 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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.
Posted
Comments
Herman<T>.Instance 25-Jul-14 16:01pm    
put a union between the queries
partha143 25-Jul-14 16:08pm    
Is there another way other then using union? My main intention was to reduce usage the sub query.
INSERT INTO newtab(Ques1, Ques2, Ques3, Ques4, Ques5) SELECT TOP(1)* FROM tab1 UNION SELECT TOP(1)* FROM tab2; This is what I did now. But Im thinkin if there any other way so that i can eliminate writing "SELECT TOP(1)* FROM tabn" for every tab?
Maciej Los 25-Jul-14 16:11pm    
What you mean by "1st 5 rows from all the tables"?
Is there any definition for 1. row?
partha143 25-Jul-14 16:15pm    
Sorry. I miss typed it. I want to select 1st row from all 5 tables and insert those 5 rows into newtab.

1 solution

Here i have used temp tables,

SQL
/*temp tbls*/
CREATE TABLE #Table1(Id BIGINT, Value VARCHAR(50))
INSERT INTO #Table1 VALUES (1, 'Value1_tbl1'), (2, 'Value2_tbl1')

CREATE TABLE #Table2(Id BIGINT, Value VARCHAR(50))
INSERT INTO #Table2 VALUES (1, 'Value1_tbl2'), (2, 'Value2_tbl2')

CREATE TABLE #ResultTable(Id BIGINT, Value VARCHAR(50))

/*show datas*/
SELECT * FROM #Table1;
SELECT * FROM #Table2;
SELECT * FROM #ResultTable;

/*drop tbls*/
--DROP TABLE #Table1
--DROP TABLE  #Table2
--DROP TABLE  #ResultTable


/*TopRowOfAllTbl holdes first rows or all tables, until do insert into #ResultTable*/
WITH TopRowOfAllTbl
AS
(
	SELECT TOP(1)* FROM #Table1
	UNION ALL
	SELECT TOP(1)* FROM #Table2
	/*here add more tables with UNION ALL*/
)
INSERT INTO #ResultTable
SELECT *
	FROM TopRowOfAllTbl;
	
	
/*see the result*/	
SELECT *
	FROM #ResultTable
 
Share this answer
 
Comments
codejet 6-Aug-14 17:50pm    
When you say first row what do you mean? What is the ordering based timestamp, id etc?
DiponRoy 7-Aug-14 0:47am    
Here the first row would be as your table is holding the data (considering the indexes if any else as a normal select shows). if any ordering needed, we have to use it inside the query.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900