Clear the database table before importing into Excel

1. Problem description

We hope to clear the data in the original database table before importing the Excel data and submitting it.

2. Implementation ideas

When setting the data entry attributes, the built-in SQL will be executed sequentially from top to bottom. Use this feature to set delete submission to clear the table in the first built-in sql, and then set smart submission or insert submission in the second built-in sql to insert data.

This method can ensure the consistency of the transaction. If the data insertion fails, the operation of emptying the table will also be rolled back.

1.Prepare the data

Create a new general report, add database query ds1, SQL statement: SELECT * FROM Product

2. Design template

Excel table used: Clear_the_database_table_before_importing_into _Excel.xlsx

In the designer, design the table according to the Excel table, and set "Cell Attributes> Expansion Direction" to "Vertical" in cell B3, as shown in the following figure:

Note: This example does not add widgets when designing the table, only supports importing data, and cannot be modified. If necessary, you can add it yourself.

Select "Individually set for the template" in "Template> Web Attributes> Data Entry Settings", and double-click "Emptying Import" to add it to the toolbar.

3. Set table emptying

Click "Template>Data Entry Attributes" to add a built-in SQL submission, and the submit type is "Delete Submit". Select the data table that needs to be cleared, add the field "product ID" and check the primary key, use the formula SQL ("FRDemo", "SELECT Product ID FROM Product", 1) to query all the product IDs as the primary key to clear the table .

Note: Because the table is cleared, only one field is needed. Setting multiple fields may cause Cartesian product problems.

4. Set insert data

Add another built-in SQL below the previous built-in SQL, the Submit Type is "Smart Submit", select the "Product" table in FRDemo,Smart Add Fields, and add the corresponding cells, and check the "Product ID" as the primary key.

5. Effect preview

Use the Clear Import button, click Submit after importing excel, the data table of the database will be cleared first and then the imported data will be inserted.

Click to download the template: Clear_the_database_table_before_importing_into _Excel.cpt