Skip to main content

Visual Query Builder - Insert Query Generation

The Visual Query Creation interface allows users to visually generate SQL queries for inserting data into a database. This provides an easy-to-use interface for building complex INSERT queries without needing to write SQL code manually.

Main Features​

1. Table Selection​

  • At the top, you can select the table that you want to insert data into.
  • Example: In the image, the billing table is selected. This table has fields like id, pk, companyid, invoiceid, paymentstatus, etc.
info

Make sure to carefully select the table and its respective fields to avoid mismatching data during the insert operation.

2. Params Section​

  • The Params section allows you to define the parameters (i.e., column names) and the values you want to insert into the selected table.
  • Example:
    • The paymentstatus field is selected, and its value is set to 'success'. This means that 'success' will be inserted into the paymentstatus column.

3. Insert Section​

  • In the Insert section, you can map the columns of the selected table to the values you wish to insert.
  • Example:
    • The columns id, pk, companyid, and companypk are mapped to the values provided. This ensures that each column receives the appropriate data during the insert operation.

4. SQL Query Tab (Automatic Query Generation)​

The SQL Query tab is where the system automatically generates the SQL code based on the visual inputs you have provided. This eliminates the need for manual SQL writing, making the process easier and less error-prone.

Key Features​

  1. Visual to Code Translation:

    • As you define parameters and map columns, the system translates these visual inputs into a corresponding SQL query.
    • You don't need to manually write the query, as the interface builds it based on your selections.

  2. Real-Time Query Updates:

    • As you modify the parameters or insert values, the SQL query updates in real time. This ensures that you always see the query that will be executed.
    • Example: If you change the value of paymentstatus to 'pending', the query will update automatically.

  3. Syntax Accuracy:

    • The system ensures that the generated SQL query follows the correct syntax for the selected database. This reduces the chances of syntax errors.

  4. Improved Efficiency:

    • The automatic query generation feature saves time and effort, especially for users inserting large amounts of data.

5. Example of Generated SQL Query​

  • Suppose you are inserting new billing information where the paymentstatus is set to 'success'.
  • The generated query might look like this: 
    INSERT INTO billing (billing.id, billing.pk, billing.companyid, billing.companypk, billing.paymentstatus) 
    VALUES ('f1d02d03-8dc9-488d-9112-eeafa888bbcb', '1', 'f1d02d03-8dc9-488d-9112-eeafa888bbcb', '23', 'success');

6. Error Prevention and Flexibility​

  • The system reduces the risk of common SQL errors like mismatched column names or missing values.
  • Although the query is automatically generated, you can modify it manually in the editor if custom logic or further adjustments are needed.

7. Validation Result​

  • The Validation Result section shows any errors or validation issues in the query. This ensures that the query is valid before execution.

Benefits of the Visual Insert Query Builder​

  • Ease of Use: The interface allows users to insert data into tables without needing SQL knowledge, making it accessible for non-technical users.
  • Error Reduction: The system generates SQL based on visual input, minimizing the chances of errors.
  • Quick Data Entry: The visual interface allows faster data insertion, especially when dealing with multiple columns or complex data structures.
Final Tip

Even though the system automatically generates SQL queries, it's a good practice to review the generated query before executing it. This ensures that the query performs exactly as intended, especially when working with large datasets or sensitive data.