Table data show one by one record different two tables?-CodePudding

Table 1

ABC	DEF
GS	PM
BS	PK

Table 2

ABC	DEF
YZ	TT
UG	KK

Need output

ABC	DEF
GS	PM
YZ	TT
BS	PK
UG	KK

So please help me sql query

CodePudding user response：

table1:

Azbuka	Def
A1	D1
A2	D2
A3	D3
A4	D4

table2:

Azbuka	Def
F1	H1
F2	H2
F3	H3
F4	H4

DECLARE @max INT

select @max = count(*) from table1

;WITH CTE AS (
    SELECT 1 num
    UNION ALL
    SELECT num 1
    FROM CTE
    WHERE num<@max
)
SELECT t1.* FROM CTE CC
inner join 
(
    Select 
        ROW_NUMBER() OVER(ORDER BY id ASC) AS RNum, 
        Azbuka, 
        Def 
    from table1
    union all 
    Select 
        ROW_NUMBER() OVER(ORDER BY id ASC) AS RNum, 
        Azbuka, 
        Def 
    from table2
) t1 on t1.RNum = CC.num

Result:

Azbuka	Def
A1	D1
F1	H1
A2	D2
F2	H2
A3	D3
F3	F3
A4	D4
F4	H4

CodePudding user response：

A more simple example and without recursive:

Select main.Azbuka, main.Def from (
    Select 
        (ROW_NUMBER() OVER(ORDER BY id ASC))*2 AS RNum, 
        Azbuka, 
        Def 
    from TEST_DB.dbo.table1
    union all 
    Select 
        ((ROW_NUMBER() OVER(ORDER BY id ASC))*2   1) AS RNum, 
        Azbuka, 
        Def 
    from TEST_DB.dbo.table2
) main 
order by main.RNum