Set-based data importation into an enterprise resource planning system

• US 20070220027A1
• Filed: 03/17/2006
• Published: 09/20/2007
• Est. Priority Date: 03/17/2006
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Sergeyev, Vladimir, Avadhanam, Srikanth, Richey, Jeffrey
• Granted Patent: US 7,873,675 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06Q 10/06 Resources, workflows, human or project management; Enterprise or organisation planning; Enterprise or organisation modelling

Abstract

Importation of data records organized within tables from a source system to a target system while preserving relationships among data records of the tables when record identification values identifying each data record have been changed during importation includes, using the same database query on an entire table to update each record identification references that refers to a data record in another table.

Claims

1. A method of importing data in an enterprise resource planning system from a source table in a first system to a target table in a second system comprising:

receiving data records organized within rows of a first table, wherein the first table comprises a first record identification reference column comprising one or more record identification references, wherein a record identification reference of the first record identification reference column relates a first data record of the first table with a record identification of a second data record;

assigning a new record identification to the second data record during importation of the second data record to the target system; and

executing a database query on the entire first table to merge the data records of the first table with a first target table within a target system, wherein executing the database query to merge the data records comprises;

merging data records of the first table with the first target table; and

updating each record identification reference of the first table to relate the corresponding data record of the first table with a record identification of a data record within the target system; and

relating the updated record identification reference of the first data record of the first target table with the new record identification of the second data record.

2. The method of claim 1, further comprising:

copying the row contents of the first table to a first temporary staging table;

organizing the second data record within a row of a second temporary staging table having a record identification column comprising one or more record identifications uniquely identifying each data record of the second staging table within the target system;

wherein updating each record identification reference of the first table during the database query comprises executing a join statement between the first record identification reference column of the first temporary staging table and the reference identification column of the second temporary staging table; and

wherein merging data records of the first table comprises merging row contents of the first temporary staging table with the first target table.

3. The method of claim 2, wherein the join statement comprises a structured query language (SQL) outer join statement.

4. The method of claim 1, wherein each record identification reference of the first record identification reference column relates a data record of the first table with a record identification of a data record of a second table within the source system,

wherein updating each record identification reference of the first table during the relational system query comprises:

updating each record identification reference of the first table to relate the corresponding data record of the first table with a new record identification of a data record of a second target table,

wherein the second target table corresponds to an importation of the second table to the target system and at least one record identification of the second table is replaced with a new record identification during importation of the second table to the target system.

5. The method of claim 1, wherein the first record identification column comprises a record identification reference relating a data record of the first table with a record identification of a data record of a second table and a record identification reference relating a data record of the first table with a record identification of a data record of a third table,

wherein updating each record identification reference of the first table during the database query comprises:

updating each record identification reference of the first table that relates to a record identification of a data record of the second table to relate the corresponding data record of the first table with a new record identification of a data record of a second target table; and

updating each record identification reference of the first table that relates to a record identification of a data record of the third table to relate the corresponding data record of the first table with a new record identification of a data record of a third target table,

wherein the second target table corresponds to an importation of the second table to the target system and at least one record identification of a data record of the second table is replaced with a new record identification during importation of the second table to the target system, and

wherein the third target table corresponds to an importation of the third table to the target system and at least one record identification of a data record of the third table is replaced with a new record identification during importation of the third table to the target system.

6. The method of claim 1, wherein a record identification reference of the first record identification reference column relates a data record of the first table with a record identification of a data record of a second table within the source system;

wherein the first table further comprises a second record identification reference column comprising one or more record identification references, wherein a record identification reference of the second record identification reference column relates a data record of the first table with a record identification of a data record of a third table within the source system;

wherein updating each record identification reference of the first table during the database query comprises;

updating each record identification reference of the first record identification reference column to relate the corresponding data record of the first table with a new record identification of a data record of a second target table; and

updating each record identification reference of the second record identification reference column to relate the corresponding data record of the first table with a new record identification of a data record of a third target table,

wherein the second target table corresponds to an importation of the second table to the target system and at least one record identification of a data record of the second table is replaced with a new record identification during importation of the second table to the target system, and

wherein the third target table corresponds to an importation of the third table to the target system and at least one record identification of a data record of the third table is replaced with a new record identification during importation of the third table to the target system.

7. The method of claim 1, wherein the first table and the first target table each comprise an equality criteria column and a record identification column, wherein values of the equality criteria column and values of the record identification column of the first table uniquely identify each imported data record and values of the equality criteria column and values of the record identification column of the first target table uniquely identify each data record within the target system, the method further comprising:

comparing the values of the equality criteria columns of the first table and the first target table to determine conflicting data records, wherein matching values comprise a conflicting record; and

merging data records of the first table with the first target table during the database query comprises merging data records of the first table with the first target table which do not comprise conflicting data records.

8. The method of claim 7, wherein merging data records of the first table with the first target table during the database query further comprises:

merging all columns except the equality criteria columns of data records of the first table with the first target table which comprise conflicting data records; and

assigning a new record identification to each conflicting data record of the first table, wherein the new record identification comprises the record identification of the corresponding conflicting data record of the first target table.

9. The method of claim 7, wherein comparing the values of the equality criteris columns comprises executing a join statement between the equality criteria columns of the first table and the first target table.

10. The method of claim 9, wherein the join statement comprises a structured query language (SQL) inner join statement.

11. The method of claim 1, wherein the first table and the first target table each comprise an equality criteria column and a record identification column, wherein values of the equality criteria column and values of the record identification column of the first table uniquely identify each imported data record and values of the equality criteria column and values of the record identification column of the first target table uniquely identify each data record within the target system, the method further comprising:

comparing the values of the equality criteria columns of the first table and the first target table to determine conflicting data records, wherein matching values comprise a conflicting record; and

assigning a new record identification to each data record of the first table which does not comprise conflicting data records, wherein the new record identification is assigned from a range of consecutive record identification values and each new record identification uniquely identifies the data record of the first target table within the target system.

12. The method of claim 1, further comprising:

copying the row contents of the first table to a first temporary staging table, wherein the first temporary staging table comprises a table schema of the target table having one or more table constraints regarding the contents of the target table; and

rejecting a data record comprising data that violates the table constraints.

13. The method of claim 1, further comprising:

comparing data values within a first column of two or more rows of the first table;

comparing data values within a second column of the two or more rows of the first table; and

rejecting at least one of the two or more rows if the data values of the two or more rows of the first column match and if the data values of the two or more rows of the second column match.

14. A computer-readable medium having computer-executable instructions for preserving relationships between data records of a first table and data records of a second table during importation of the first and second tables to a target system, the computer executable instructions comprising instructions for:

copying data records of the first table to a first temporary staging table;

copying data records of the second table to a second temporary staging table;

assigning new record identifications to the data records of the second temporary staging table, wherein each new record identification uniquely identifies the data record within the target system; and

performing a join operation between a first record identification reference column of the first temporary staging table and a record identification column of the second temporary staging table, wherein the first record identification reference column comprises record identifications of the data records of the second table and the record identification column of the second temporary staging table comprises the new record identifications of the data records of the second temporary staging table.

15. The computer-readable medium having computer-executable instructions of claim 14, wherein the join operation comprises an outer join operation.

16. The computer-readable medium having computer-executable instructions of claim 14, wherein the first record identification reference column comprises one or more record identifications of data records of the second table and one or more record identifications of data records of a third table, the computer executable instructions further comprising instructions for:

copying data records of the third table to a third temporary staging table;

assigning new record identifications to the data records of the third temporary staging table, wherein each new record identification uniquely identifies the data record within the target system;

performing a join operation between the first record identification reference column of the first temporary staging table and a record identification column of the third temporary staging table, wherein the record identification column of the third temporary staging table comprises the new record identifications of the data records of the third temporary staging table.

17. The computer-readable medium having computer-executable instructions of claim 14, the computer executable instructions further comprising instructions for:

copying data records of a third table to a third temporary staging table;

assigning new record identifications to the data records of the third temporary staging table, wherein each new record identification uniquely identifies the data record within the target system;

performing a join operation between a second record identification reference column of the first temporary staging table and a record identification column of the third temporary staging table, wherein the second record identification reference column comprises record identifications of the data records of the third table and the record identification column of the third temporary staging table comprises the new record identifications of the data records of the third temporary staging table.

18. The computer-readable medium having computer-executable instructions of claim 14, wherein the first temporary staging table comprises a plurality of equality criteria columns within the first temporary staging table, the computer executable instructions further comprising instructions for:

comparing the values of two or more of the plurality of equality criteria columns; and

deleting at least one of the two or more data records if the values of the two or more equality criteria columns match.

19. A computer adapted for importing data records of a source table to a target table within a target system, wherein each data record is organized within a row of the table, the computer comprising:

a network communication device for receiving data via a network;

a memory storing machine-readable instructions; and

a processor for executing the machine-readable instructions performing a method comprising;

copying the rows of the source table to a temporary staging table, wherein each row comprises a record identification value uniquely identifying each data record within the source system;

executing a structured query language (SQL) inner join statement between an equality criteria column of the temporary staging table and an equality criteria column of the target table to determine conflicts between data records of the temporary staging table and data records of the target table, wherein the equality criteria column of the temporary staging table comprises values uniquely identifying each data record within the source system and the unique identity column of the target table comprises values uniquely identifying each data record within the target system;

executing a structured query language (SQL) inner join statement between a record identification column of the temporary staging table comprising record identification values from the source system and a record identification column comprising new record identification values for the target system;

executing a structured query language (SQL) outer join statement between a record identification reference column of the temporary staging table comprising record identification values of another table from the source system and a record identification column of another temporary staging table comprising new record identification values for the target system, wherein the other temporary staging table corresponds to the other table from the source system.

20. The computer of claim 19, wherein the memory stores instructions for performing the method further comprising:

executing a structured query language (SQL) outer join statement between a first row of a first column of the temporary staging table and a second row of the first column of the temporary staging table to determine the equivalency of values between the first row and the second row within the first column; and

executing a structured query language (SQL) outer join statement between the first row of a second column of the temporary staging table and the second row of the second column of the temporary staging table to determine the equivalency of values between the first row and the second row within the second column; and

executing a structured query language (SQL) delete statement to delete the first row if the values between the first row and the second row within the first column match and if the values between the first row and the second row within the second column match.