Question:
How can I transfer data between two different tables from a query with Elipse E3?
Solution:
The object E3Query helps setting up queries in the application’s database. This object displays a friendly interface, in order to allow creating SQL queries with a graphic interface that instantly follow up the generated SQL code. Additionally, E3Query allows showing data from the last n days, hours, or months, depending on the filter that was implemented. This information is displayed in tables, and their data can be transferred from one table to another.
When you transfer data between tables, there are two possibilities:
- both tables display the same columns structure; or
- the first and second table only share a few columns in common.
That is to say, each case will require a different type of solution.
In the first example, when both tables share the same columns structure, you will need to use the following SQL syntax in a query:
INSERT INTO SecondTable SELECT * FROM FirstTable
On the other hand, the second example shows two tables where only a few columns are similar. In that case, you will need to add these columns to the code, as seen below:
INSERT INTO SecondTable (ColumnName01, ColumnName02, ColumnName03 ,...) SELECT ColumnName01, ColumnName02, ColumnName03 ,... FROM FirstTable
In the latter case, the name of the columns don’t have to be the same; you will only need to inform where they come from and where they go to.
If you need to use a condition in order to transfer data, you can use the expression WHERE; for further information, we suggest reading the related articles below.
Related articles:
- Structure Query Language (SQL): Chapter 6 – SQL Commands.
- KB-28190: Filter in SQL query using time value.
- KB-33119: Query filter.
