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


...

3 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