AUTOMATION
SQL

Automating SQL Queries with SQL CLI and SQL Job Scheduling

intro

Assume you manage inventory in an e-commerce company. Due to the increased number of orders, manually updating the inventory is becoming time-consuming and leading to mistakes such as typos or incorrect quantities.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

To improve productivity and data accuracy, you can automate the process of updating inventory levels based on incoming orders. Automating the e-commerce inventory management will ensure that product availability is up-to-date without manual intervention.

In this tutorial, you will learn how to automate the process of e-commerce inventory management using DbVisualizer, an SQL CLI and SQL job scheduling tool.

Prerequisites

To follow along with this tutorial, you will need the following:

  • DbVisualizer, a database SQL client installed on your local machine. To install DbVisualizer, navigate to the DbVisualizer download page. Then, download the recommended installer for your operating system. After that, execute the installer you downloaded and follow the instructions.
  • A database management system. In this case, we will use Postgres. To install PostgreSQL, navigate to the PostgreSQL download page and download it for your operating system. You can follow this guide to install PostgreSQL on Windows, this guide to install it on Linux, and this guide to install it on macOS.
  • A basic understanding of SQL syntax and database management is also required.

We will start by connecting Postgres to DbVisualizer, creating a database for the e-commerce store, and importing data into the database.

Connecting Postgres to DbVisualizer

Step 1: Start DbVisualizer and click the Create a Connection button as shown below.

Creating a connection in DbVisualizer
Creating a connection in DbVisualizer

Step 2: Search for Postgres and double-tap the Postgres driver from the popup menu on the left side of your screen, as shown below.

Searching for the Postgres Driver in DbVisualizer
Searching for the Postgres Driver in DbVisualizer

An object view tab for the Postgres connection is opened.

The PostgreSQL connection Object View Tab
The PostgreSQL connection Object View Tab

Step 3: Fill in the database connection name field with “AutomatingSQL,” or the name of your choice as shown below.

Filling the Database Connection Name Field in DbVisualizer.
Filling the Database Connection Name Field in DbVisualizer.

Fill in the Database Userid field with “postgres” and the Database Password field with your password (we use “test123”, you should use a stronger one) as shown below.

Filling the Authentication Tab in DbVisualizer.
Filling the Authentication Tab in DbVisualizer.

Once done, click the Connect button at the bottom, and if there are any issues, they will be displayed under the Connection Message section.

Postgres Connection Success Message in DbVisualizer.
Postgres Connection Success Message in DbVisualizer.

If the connection is successful, you should see the newly created AutomatingSQL connection on the left sidebar of your screen.

Creating a database for the e-commerce store

Creating a database in Postgres using DbVisualizer is a simple and straightforward process. Follow the steps below to learn how to create a database in Postgres using DbVisualizer.

Step 1: Open the  AutomatingSQL tab tree, right-click on  Databases, and select the Create Database option as shown below.

Selecting the **Create Database** option in DbVisualizer.
Selecting the Create Database option in DbVisualizer.

Step 2: On the pop-up window that appears, fill in the fields as shown below, and then click the Execute button to create the database.

Filling new database fields in DbVisualizer.
Filling new database fields in DbVisualizer.

If you look at the  AutomatingSQL connection, you will see the database ecommercedb has been created.

Database ecommercedb created in DbVisualizer.
Database ecommercedb created in DbVisualizer.

Next, we need to import data into the ecommercedb using DbVisualizer.

Importing data into the ecommercedb database

Now that you have learned how to create a database in Postgres using DbVisualizer, let’s now import some data into the database by following the steps below.

Step 1: Navigate to this Google Drive Link and download the eCommerce store inventory dataset.

*eCommerce store inventory dataset in Google Drive.*
eCommerce store inventory dataset in Google Drive.

Step 2: Open the database connection AutomatingSQL in DbVisualizer. To import data, right-click on the Tables tab tree and select Import Table Data, as shown below.

Selecting the Import Table Data option in DbVisualizer.
Selecting the Import Table Data option in DbVisualizer.

Step 3: On the popup window, navigate to the directory you downloaded the eCommerce store inventory dataset CSV file into, select the products file and click the Open button.

*Navigating to the directory you downloaded the eCommerce store inventory dataset CSV files.*
Navigating to the directory you downloaded the eCommerce store inventory dataset CSV files.

Step 4: Keep clicking the Next button below the popup window until you reach the window below where you need to create a New Database Table.

Creating a New Database Table in DbVisualizer.
Creating a New Database Table in DbVisualizer.

Step 5: On the popup window, give the table that will hold the products data a name. In this case, I have named my table products as shown below.

Giving a table a name in DbVisualizer.
Giving a table a name in DbVisualizer.

Step 6: Next, check the Use Delimited Identifiers checkbox at the bottom of the pop-up window to help PostgreSQL differentiate any column name from its reserved keywords.

Checking the Use Delimited Identifiers checkbox.
Checking the Use Delimited Identifiers checkbox.

Step 7: Click the Next button and then import the dataset into the ecommercedb database. If the data is imported successfully, you will get a Success message, as shown below.

A Success message in DbVisualizer.
A Success message in DbVisualizer.

Step 8: Repeat the same process and import table data from the orders CSV file to create an orders table. Once done, right-click the Tables tab and select Refresh Objects tree.

Refreshing the table objects tree in DbVisualizer
Refreshing the table objects tree in DbVisualizer

You should see the products and orders tables created, as shown below.

Products and orders tables in [DbVisualizer](https://www.dbvis.com/eval/).
Products and orders tables in DbVisualizer

Products and orders tables in DbVisualizer.

Next, let us create an SQL file that updates the inventory_level in the products table by subtracting the ordered quantity from the current inventory level for each product in the orders table.

Creating the SQL file

In this section, you will learn how to create an SQL file that contains an SQL query that manages the e-commerce inventory.

Step 1: Write an SQL query that updates the inventory levels based on incoming orders. Here is an example.

Copy
        
1 UPDATE products 2 SET inventory_level = inventory_level - o.quantity 3 FROM orders o 4 WHERE products.product_id = o.product_id;

The SQL query above updates the inventory_level in the products table by subtracting the ordered quantity from the current inventory level for each product in the orders table.

Step 2: Open an SQL editor in DbVisualizer using the (Ctrl+T) keys, and execute the query above using the (Ctrl+Enter)  keys to test if it works.

Executing query on a SQL editor in DbVisualizer
Executing query on a SQL editor in DbVisualizer

Step 3: Create an SQL file using (Ctrl+S) keys and name the file inventorymanagement. Then click the save button to save the file.

Creating an SQL file in DbVisualizer
Creating an SQL file in DbVisualizer

Generating an automation script

In this section, you will learn how to create an automation script that contains the SQL file created earlier to manage the e-commerce inventory.

Step 1: Open the command line script generator using the (Ctrl+Alt+G) keys in DbVisualizer.

Opening command line script generator in DbVisualizer
Opening command line script generator in DbVisualizer

Step 2: Select the Use Anonymous Connection option on the script generator. An anonymous connection will enable and disable connection options accordingly.

Select the Use Anonymous Connection option in DbVisualizer
Select the Use Anonymous Connection option in DbVisualizer

Step 3: Click the Copy button to copy the script generated.

Copying the script generated in DbVisualizer
Copying the script generated in DbVisualizer

Step 4: Open the command line and paste the script copied.

Pasting a script on a command line
Pasting a script on a command line

Step 5: In the script, change the -password “#UNDEFINED” to -password “test123” or to the password of your database connection. Note that providing passwords over the CLI isn't very secure. Passwords can be observed by anyone who reads the CLI history.

Adding password to the script
Adding password to the script

Step 6: Press Enter to test the script works. If it works, you should get [1 successful, 0 errors] message on the command line as shown below.

Testing if the script works
Testing if the script works

Next, let us schedule the script to run every day using the Task Scheduler.

Scheduling query execution using Windows Task Scheduler

Step 1: Start the Windows Task Scheduler and choose the action "Create Basic Task".

Starting Windows Task Scheduler
Starting Windows Task Scheduler

Step 2: Give the task a descriptive name (e.g. EcomInventoryManagement) and press Next.

Give a task a descriptive name
Give a task a descriptive name

Step 3: Choose the trigger type (e.g. daily) and press Next

Choosing a trigger type
Choosing a trigger type

Step 4: Enter the start time and value press Next (here we set up a task to recur every day):

Entering start time and value
Entering start time and value

Step 5: Specify the action to perform and click Next.

Specifying the action to perform
Specifying the action to perform

Step 6: Copy the script you run on the command line. Then paste it under the Program/Script option and click next.

Starting a program
Starting a program

Step 7: Click the Yes button on the popup that appears.

Accepting to run a program
Accepting to run a program

Step 8: Click the Finish button to schedule the script execution.

Scheduling the script execution
Scheduling the script execution

Conclusion

In conclusion, automating SQL query execution through the use of tools like DbVisualizer, an SQL CLI, and SQL job scheduling provides a streamlined process to database management. By integrating these tools, businesses or organizations can enhance productivity and data accuracy.

We hope that you’ve enjoyed this blog and that you will stick around for more content - have a read through other blogs on our website, and we’ll see you in the next one.

Dbvis download link img
About the author
Bonnie
Bonnie
Bonnie is a web developer and technical writer creating easy-to-understand technical articles.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Derived Table: Everything You Need to Know

author Antonello Zanini tags MySQL POSTGRESQL SQL SQL SERVER 9 min 2025-02-06
title

Understanding MVCC in MySQL

author Lukas Vileikis tags DbVisualizer MySQL SQL 7 min 2025-02-05
title

Postgres Create Array of Numbers: Complete Guide

author Lukas Vileikis tags POSTGRESQL SQL 7 min 2025-02-03
title

SQL Injection Cheat Sheet: SQLi 101

author Lukas Vileikis tags MySQL SECURITY SQL 10 min 2025-01-29
title

SQL PARTITION BY in PostgreSQL: A Guide to Window Functions and Data Segmentation

author Leslie S. Gyamfi tags SQL 7 min 2025-01-28
title

A Complete Guide to the ALTER TABLE DROP COLUMN Statement

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2025-01-27
title

How to Use a Recursive CTE in SQL

author Antonello Zanini tags MySQL POSTGRESQL SQL SQL SERVER 5 min 2025-01-22
title

Guidelines for a Proper SQL Script: ACID, CRUD, and Other Things You Need to Know

author Lukas Vileikis tags DbVisualizer MySQL OPTIMIZATION SQL 6 min 2025-01-21
title

How to Use MERGE in SQL Query Statements: Complete Guide

author Antonello Zanini tags ORACLE POSTGRESQL SQL SQL SERVER 8 min 2025-01-20
title

Understanding the SQL UNIQUE Constraint

author Leslie S. Gyamfi tags DbVisualizer SQL 6 min 2025-01-07

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.