Single query to copy data from 3 tables into a single empty table

Question

In an interview today, I was asked if I could write a single query to copy data from 3 source tables to an empty destination table.

I started saying "I will use a temp table or table variables", however the interviewer said "no, I want to see a single statement or query."

I was blank :(

Can someone share the right answer?

Answer 1

Step 1: First prepare the data set through sql query as per the requirement.

Step 2: Insert the prepared record set into a new table.

Note: In 'Union All' number of columns in select list for all the table must be same.

Note : In JOIN you can prepare the data from different table through through joins as per business requirement and insert into a new table.

For Ex:

select * into newtable from
( 
select * from table1
union all
select * from table2
union all
select * from table3
) A