如何选择每个表的第1行并将该数据插入新表

问题描述

我有5个表（tab1，tab2，tab3，tab4和tab5），所有5个表都有相同的表

定义。我想选择每个表的第一行，并在

新表（newtab）中插入这5行。我只能编写从一个表中选择1行的查询，并且

将其插入到newtab中。如何从所有表中选择前5行并在一个查询中将它们插入newtab中。

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.

推荐答案

/*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

这篇关于如何选择每个表的第1行并将该数据插入新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！