Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Maybe with "select into"? – Amiram Korach Jul 19 '12 at 11:12
Perhaps: INSERT INTO EmptyTable (SELECT ... UNION SELECT ...) ; – ypercubeᵀᴹ Jul 19 '12 at 12:01
Just curious, what kind of position was the interview about? – ypercubeᵀᴹ Jul 19 '12 at 13:20

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
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.