SQL “Select Into” Statement Usage Tutorial

RSS
EMAIL
FACEBOOK
FACEBOOK
GOOGLE
GOOGLE
https://www.poftut.com/sql-select-into-statement-usage-tutorial/
PINTEREST
PINTEREST
INSTAGRAM

SQL provides different statements in order to select into existing table, variable, temp table in various database servers like Oracle, MySQL, PostgreSQL etc. In this tutorial we will examine how to use Select Into SQL statement for different cases.

Syntax

Select Into statement syntax is the same as with Select syntax. We just need to a INTO between SELECT and FROM. We can also use WHERE statement in order to filter rows.

  • columns specifies the columns in the oldtable we want to copy. We can also use * in order specify all columns.
  • newtable is the new table we want to create or copy.
  • externaldb is optional. We can use if we will copy from another or external database.
  • oldtable is table we want to copy from.
  • condition can be used if we want to filter or specify the data we want to copy. It is the same fashion with regular WHERE usage.

Copy All Columns Into New Table

We will start with a simple and generic example. We will copy all columns from table named customer into table names allcustomers .

Copy Specified Columns Into New Table

We can also specify the columns we want to into new table. In this example we will copy columns named id , name from table customer into table named allcustomers

Create Backup Copy Of Table

Another useful scenario of Select Into statement is creating backup of a table by copying is fully another table. We will create backup of table named customers with the name of cusotmersbackup

Copy Table From Another Database

The default behavior of Select Into statement is copy tables in the current database. If we need to copy table into new database we need to use In statement. In this example we will copy table named customers into new database newdb with the table name newcustomers .

LEARN MORE  How Prompt for Input in Bash

Copy Only Specified Rows Into New Table

As stated previously we can copy only specified or filtered rows during Select Into . We will use WHERE clause with the condition we want to apply. In this example we will copy users with Country Turkey from table customers into new table allcustomers .

Copy From More Than One (Multiple) Table Into New Table

We can use JOIN with the SELECT INTO . This will provide us to copy from multiple tables into new table. In this example we will copy from tables customers and deals into customer_deal table with LEFT JOIN on customerid

Create New Empty Table

Another useful usage case is creating new empty table with the given table structure and columns. We will use standard syntax and filter wit 5=1 which is always false and do not copy any data from source table. In this example we will create a new table allcustomer from table customers .

RSS
EMAIL
FACEBOOK
FACEBOOK
GOOGLE
GOOGLE
https://www.poftut.com/sql-select-into-statement-usage-tutorial/
PINTEREST
PINTEREST
INSTAGRAM