John needs to transfer a data set of 50,000 tuples from database schema1 to data
ID: 3746131 • Letter: J
Question
John needs to transfer a data set of 50,000 tuples from database schema1 to database schema2. Database schema1 contains table1, which has seven attributes and database schema2 contains table2, which has five attributes. How will he perform the transfer of the data set of 50,000 tuples from database schema1 to database schema2 at the database level? Which fields will be kept, lost, or combined? Please provide example SQL diagrams and show all design work to support your response. Among all techniques available which one is the most efficient? Why?
Explanation / Answer
Solution:
Let schema1.table1 has following columns:
table1(col1,col2,col3,col4,col5, updatedBy, updatedAt)
and schema2.table2 as following columns:
table2(col1,col2,col3,col4,col5)
We can insert the data from table2 to table1 using programming language. But it will be slow as there a loop will may be used to iterate over the records of one table and inserting that record to another table.
Insert select query can be written to perform this operation at database level if the col1 to col5 of table1 have same datatypeand length as of table2 to avoid data loss.
It is efficient and it can be executed in single query.
Following is the query to use insert select.
Insert into schema1.table1(col1,col2,col3,col4,col5, updatedBy, updatedAt)
select
t2.col1, t2.col2,t2.col3,t2.col4,t2.col5, ‘user1’, now());
If required, any column can be left. It can be omitted from insert statement and select statement but the number of columns mentioned in insert and select statements should be same and columns should have same data type.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.