MySQL : Data Migration Techniques - DevDummy

Latest

Views | Thoughts | Concepts | Techniques

Sunday, November 25, 2018

MySQL : Data Migration Techniques


How to Insert Data to a Table by Selecting from a Different Table

It is often required to insert data in to a table selectively fetching data from a  different table. This is a crucial requirement specially on data migrations, to transfer data into a temporary table or to transfer table under different domain.

Fortunately MySQL facilitates this very conveniently by providing built in functionality,  INSERT ... SELECT .

INSERT ... SELECT Syntax

MySQL INSERT with SELECT Syntax is very straightforward, where the select statement is appended to the Insert Statement with proper column name mappings.

The below example shows a query to insert book data in to a Library Book table from regular book table with proper column mappings which are published after the year 2000,


INSERT INTO library_book (id, name, isbn, publisher, published_year)
  SELECT id, title, publisher, year
  FROM book WHERE book.published_year > 2000 ;

However in the above example you may have noticed that id column is directly mapped. This behaviour is exactly aligned with the requirement and only needed only if you want to make the relation in between other migrated tables. 

For an example, if you have migrated other tables which refers to library book table where it was referring to the old book table, the id should be consistent. If it is not the case, you can ignore it allowing to auto increment or set UUID() function to set an UUID value instead.

However you have to make sure the mappings and the column data types are compatible in between.

References


...

8 comments:

  1. What's up to every body, it's my first pay a quick visit of this weblog; this webpage consists of
    awesome and in fact excellent data for visitors.

    ReplyDelete
  2. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. liberation of the soul book

    ReplyDelete
  3. The frenzy doesn't stop with the IT division. For other organization directors, IT changes implies hours or long stretches of preparing, vacation and loss of profitability that accompanies IT issues, disappointments, or framework redesigns. ExcelR Data Science Courses

    ReplyDelete
  4. Nice post. Thanks for sharing! I want people to know just how good this information is, It’s interesting content and Great work.
    360DigiTMG digital marketing course hyderabad

    ReplyDelete
  5. I finally found great post here.I will get back here. I just added your blog to my bookmark sites. thanks.Quality posts is the crucial to invite the visitors to visit the web page, that's what this web page is providing.

    business analytics course

    data analytics courses in mumbai

    data science interview questions

    data science course in mumbai

    ReplyDelete
  6. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.

    data science course

    ReplyDelete
  7. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data Science Course

    ReplyDelete
  8. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete