-4

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?

3
3

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
0

You can do this with data modifying common table expressions.

https://www.postgresql.org/docs/9.1/queries-with.html

It will run as a single query with multiple inserts to the same table from different sources.

with 
ins1 as (
    insert into dest(id, name)
    select id, name
    from source1
)
,ins2 as (
    insert into dest(id, name)
    select id, name
    from source2
)
,ins3 as (
    insert into dest(id, name)
    select id, name
    from source3
)
select l;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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