1 Introduction

In my last post (Views) I showed how to save the output of a SELECT statement as a view and how to continue working with created views.

As we know from this post, views are virtual tables. Now we want to create real tables using SELECT statements.

For this I use the same original tables as I used in my post about Views.

2 Preparation

For the following examples, I set up a new database.

SET LANGUAGE ENGLISH


CREATE DATABASE Tables_DB;

USE Tables_DB;

Below I will create 2 example tables that we will continue to work with.

Table 1:

CREATE TABLE Employees
    (ID_empl INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL)
    ;


INSERT INTO Employees VALUES ('Klara', 'Brown')
INSERT INTO Employees VALUES ('Jessy', 'Williams')
INSERT INTO Employees VALUES ('Emily', 'Taylor')
INSERT INTO Employees VALUES ('Max', 'Steel')
INSERT INTO Employees VALUES ('Alex', 'Smith')
INSERT INTO Employees VALUES ('Marc', 'Pike')
INSERT INTO Employees VALUES ('Tom', 'Hope')
INSERT INTO Employees VALUES ('Sven', 'Evergreen')
INSERT INTO Employees VALUES ('Mike', 'Owen')
INSERT INTO Employees VALUES ('Sarah', 'Pike')

SELECT * FROM Employees;

Table 2:

CREATE TABLE Employees_Metadata
    (ID_meta INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    ID_empl INT NOT NULL FOREIGN KEY REFERENCES Employees(ID_empl),
    Age INT NOT NULL,
    Salary INT NOT NULL,
    Department VARCHAR(100) NOT NULL)
    ;


INSERT INTO Employees_Metadata VALUES (1, 27, 65000, 'Dep_2')
INSERT INTO Employees_Metadata VALUES (2, 40, 55000, 'Dep_3')
INSERT INTO Employees_Metadata VALUES (3, 35, 70555, 'Dep_1')
INSERT INTO Employees_Metadata VALUES (4, 32, 65000, 'Dep_2')
INSERT INTO Employees_Metadata VALUES (5, 40, 90000, 'Dep_2')
INSERT INTO Employees_Metadata VALUES (6, 19, 45000, 'Dep_2')
INSERT INTO Employees_Metadata VALUES (7, 35, 75000, 'Dep_1')
INSERT INTO Employees_Metadata VALUES (8, 55, 110000, 'Dep_1')
INSERT INTO Employees_Metadata VALUES (9, 22, 55000, 'Dep_2')
INSERT INTO Employees_Metadata VALUES (10, 18, 35000, 'Dep_3')

SELECT * FROM Employees_Metadata;

3 Join the tables

SELECT * FROM Employees;
SELECT * FROM Employees_Metadata;

Again I write a SQL query at the beginning and want to save the output as a separate table.

SELECT t1.ID_empl,
       t1.First_Name,
       t1.Last_Name,
       t2.Age,
       t2.Salary,
       t2.Department
    FROM Employees AS t1
    LEFT JOIN Employees_Metadata AS t2
        ON t1.ID_empl = t2.ID_empl
    ;

4 Generation of new Tables

SELECT * INTO Employees_Full
    FROM(
    SELECT t1.ID_empl,
           t1.First_Name,
           t1.Last_Name,
           t2.Age,
           t2.Salary,
           t2.Department
        FROM Employees AS t1
        LEFT JOIN Employees_Metadata AS t2
            ON t1.ID_empl = t2.ID_empl) AS sub
        ;

SELECT * FROM Employees_Full;

SELECT ID_empl, 
       First_Name, 
       Salary
    INTO Employees_Reduced
    FROM Employees_Full
    WHERE Salary < 70000
    ORDER BY Salary DESC
    ;

SELECT * FROM Employees_Reduced;

CREATE VIEW Employees_Full_View AS
    SELECT t1.ID_empl,
           t1.First_Name,
           t1.Last_Name,
           t2.Age,
           t2.Salary,
           t2.Department
        FROM Employees AS t1
        LEFT JOIN Employees_Metadata AS t2
            ON t1.ID_empl = t2.ID_empl
    ;

SELECT * FROM Employees_Full_View;

SELECT ID_empl, 
       First_Name, 
       Salary
    INTO Employees_Reduced_from_View
    FROM Employees_Full_View
    WHERE Salary < 70000
    ORDER BY Salary DESC
    ;

SELECT * FROM Employees_Reduced_from_View;

5 A look at the Object Explorer

Here is another quick look at the Object Explorer. All created tables and views are listed there.

6 Conclusion

In this post I showed how to save outputs of SELECT statements as real tables.