Data migration and table manipulation in a database management system

Patent metadata

Field	Value
Publication number	US-9977798-B2
Application number	US-201514803230-A
Country	US
Kind code	B2
Filing date	Jul 20, 2015
Priority date	Jul 20, 2015
Publication date	May 22, 2018
Grant date	May 22, 2018

Abstract

A method for migrating data between tables. The method includes, receiving a Structured Query Language (SQL) statement, where the SQL statement identifies a source table and a destination table, and the source table contains source data divided into columns with a set of column names. A database management system identifies a set of instructions for manipulating the source data. The database management system generates an interim SQL statement, based on the SQL statement. The database management system executes the interim SQL statement, which copies the manipulated source data into a set of columns of the destination table.

First claim

What is claimed is:

1. A method for migrating data between tables, the method comprising:
receiving, by one or more processors, a Structured Query Language (SQL) statement, wherein the SQL statement identifies a source table and a destination table, and wherein the source table comprises a plurality of source data divided into columns with a set of source column names;
identifying at least one set of instructions associated with the SQL statement, wherein the at least one set of instructions further comprise: instructions for manipulating the plurality of source data;
generating an interim SQL statement, based on the SQL statement;
responsive to identifying the least one set of instructions associated with the SQL statement, manipulating the plurality of source data and the destination table by executing a plurality of clauses, wherein the plurality of clauses contain functional syntax comprising at least one of: migrating contents of the plurality of source data;
executing the interim SQL statement, wherein executing the interim SQL statement comprises: copying the manipulated plurality of source data into a set of destination columns of the destination table; and
migrating the contents of the plurality of source data to the set of destination columns of the destination table, wherein the migrated contents derive from the plurality of clauses.

2. The method of claim 1 , wherein manipulating the destination table comprises:
determining whether the destination table has a structure and a set of destination column names; and
in response to determining the destination table does not have a structure and the set of destination column names, copying a structure of the source table and the set of source column names into the destination table.

3. The method of claim 2 , further comprising:
in response to determining the destination table has a structure and the set of destination column names, modifying the structure of the destination table and the destination column names, wherein modifying the structure of the destination table further comprises at least one of:
adding at least one column to the destination table; and
removing at least one column from the destination table.

4. The method of claim 1 , wherein generating the interim SQL statement comprises:
utilizing the plurality of clauses, wherein the plurality of clauses comprises: a SELECT clause and an INSERT INTO clause;
updating, based on the at least one set of instructions associated with the SQL statement, the SELECT clause and the INSERT INTO clause, wherein, the SELECT clause comprises instructions for selecting at least one column name from the set of source column names to be copied into the destination table and wherein the INSERT INTO clause comprises instructions for inserting the manipulated plurality of source data into the set of destination columns of the destination table.

5. The method of claim 1 , wherein the at least one set of instructions associated with the SQL statement, further comprises:
identifying a first clause among the plurality of clauses, wherein the first clause comprises a name element and an expression element,
determining whether the name element is substantially similar to at least one column name of the set of source column names,
in response to determining that the name element is substantially similar to at least one column name of the set of source column names, replacing the at least one column name of the set of source column names with the expression element of the first clause; and
in response to determining that the name element is not substantially similar to at least one column name of the set of source column names:
adding the name element, of the first clause, to a set of destination column names;
adding a column to the set of destination columns; and
adding the expression element, of the first clause, to the set of source column names.

6. The method of claim 1 , wherein the at least one set of instructions associated with the SQL statement, further comprises:
identifying a second clause among the plurality of clauses, wherein the second clause comprises a name element;
determining whether the name element is substantially similar to at least one column name of the set of source column names;
in response to determining that the name element is substantially similar to the at least one column name of the set of source column names, removing a column, associated with the substantially similar column name of the set of source column names, from the destination table.

7. The method of claim 1 , wherein the at least one set of instructions associated with the SQL statement, further comprises:
identifying a third clause among the plurality of clauses, wherein the third clause comprises a name element;
determining whether the name element is substantially similar to at least one column name of the set of source column names; and
in response to determining that the name element is substantially similar to at least one column name of the set of source column names:
maintaining a column in the destination table, wherein the column in the destination table is associated with the substantially similar column name of the set of source column names, and
removing the plurality of source data corresponding to the substantially similar column name of the set of source column names, from the source table.

8. A computer program product comprising:
a computer readable storage medium and program instructions stored on the computer readable storage medium, the program instructions comprising:
program instructions to receive, by one or more processors, a Structured Query Language (SQL) statement, wherein the SQL statement identifies a source table and a destination table, and wherein the source table comprises a plurality of source data divided into columns with a set of source column names;
program instructions to identify at least one set of instructions associated with the SQL statement, wherein the at least one set of instructions further comprise: instructions for manipulating the plurality of source data;
program instructions to generate an interim SQL statement, based on the SQL statement;
responsive to identifying the least one set of instructions associated with the SQL statement, program instructions to manipulate the plurality of source data and the destination table by executing a plurality of clauses, wherein the plurality of clauses contain functional syntax comprising at least one of: program instructions to migrate contents of the plurality of source data;
program instructions to execute the interim SQL statement, wherein the program instructions to execute the interim SQL statement comprise: program instructions to copy the manipulated plurality of source data into a set of destination columns of the destination table; and
program instructions to migrate the contents of the plurality of source data to the set of destination columns of the destination table, wherein the migrated contents derive from the plurality of clauses.

9. The computer program product of claim 8 , program instructions to manipulate the destination table comprise:
program instructions to determine whether the destination table has a structure and a set of destination column names; and
in response to determining the destination table does not have a structure and a set of destination column names, program instructions to copy a structure of the source table and the set of source column names into the destination table.

10. The computer program product of claim 9 , further comprising:
in response to determining the destination table has a structure and a set of destination column names, program instruct

Assignees

• IBM

Inventors

• Bester Karla
• Chandler Allan T
• Shewell Mark A
• Yates Stephen J

Classifications

• G06F16/214 Primary

  Database migration support · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F17/30339

  Physics · mapped topic

• G06F17/303 Primary

  Physics · mapped topic