DEV Community

pooyaalamdari
pooyaalamdari

Posted on

SELECT - INSERT INTO SELECT

Image description

Certainly! Let's create a practical example involving a source table and a target table. Imagine we have an e-commerce platform with a table that keeps track of all the products, and we want to create a promotional campaign for products that have been highly rated by customers.

First, we'll create a source table named Products that contains product information, including ratings:

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(255),
    Rating DECIMAL(3, 2)
);
Enter fullscreen mode Exit fullscreen mode

Let's assume the Products table has the following data:

ProductID ProductName Rating
1 Laptop 4.5
2 Smartphone 4.7
3 Headphones 4.6

Next, we'll create a target table named Promotions where we want to insert products with a rating of 4.5 or higher for a special discount campaign:

create Promotions table without any values! it should be empty!(not always just for our purpose :) ) 

CREATE TABLE Promotions (
    ProductID INT,
    ProductName VARCHAR(255)
);
Enter fullscreen mode Exit fullscreen mode

Now, we'll use the INSERT INTO SELECT statement to insert the ProductID and ProductName from the Products table into the Promotions table for products with a rating of 4.5 or higher:

INSERT INTO Promotions (ProductID, ProductName)
SELECT ProductID, ProductName
FROM Products
WHERE Rating >= 4.5;
Enter fullscreen mode Exit fullscreen mode

After executing this query, the Promotions table will contain the following data:

ProductID ProductName
1 Laptop
2 Smartphone
3 Headphones

This is a practical example of how the INSERT INTO SELECT statement can be used in the real world to manage promotions based on product ratings in an e-commerce scenario.

Top comments (0)