Loop all the tables using whileloop

Question

I have a some set of 4 tables policy table, coverage table, vehicle table and driver tables.

Policy table example

policy number	record lauyout
Abc	polr
Efg	polr

Coverage table example

Policy number	record layout
Abc	prop
Abc
Efg	prop

Vehicle table example

Policy number	record layout	vin number
Abc	prp1	123
Abc	prp1	123
Efg	Prp1	456

Driver table example

Policy number	record layout	vin number
Abc	subj	123
Abc	subj	123
Efg	subj	456

I need to fetch first policy record then associated coverage then associated vehicle record then associated driver record. I have to loop all the tables and need to write the logic using SQL whileloop. Please give me some guidelines to achieve this one.

Desired output:

Policy Number	Record Layout	VIn number
Abc	polr	null
Abc	prop	null
Abc	prp1	123
Abc	subj	123
Abc	subj	123
Efg	polr	null
Efg	prop	null
Efg	prp1	456
Efg	subj	456

Answer 1

You don't need loops, a simple union all query with an additional, hard-coded value for the purpose of sorting the results will give you your desired results:

;WITH CTE AS
(
    SELECT [Policy Number], [Record Layout], NULL as [VIn number], 1 As [Table Order]
    FROM Policy
    UNION ALL 
    SELECT [Policy Number], [Record Layout], NULL, 2
    FROM Coverage 
    UNION ALL 
    SELECT [Policy Number], [Record Layout], [VIn number], 3
    FROM vehicle 
    UNION ALL 
    SELECT [Policy Number], [Record Layout], [VIn number], 4
    FROM Driver 
)

SELECT [Policy Number], [Record Layout], [VIn number]
FROM CTE
ORDER BY [Policy Number], [Table Order]