SQL
Copying tables in SQL involves duplicating the structure and data from one table to another. SELECT INTO statement in SQL allows us to create a new table based on the result of a query. This is particularly useful when we want to duplicate data from an existing table or when we need to create a table with a specific set of columns based on certain criteria.
The basic syntax of the SELECT INTO command is as follows:
SELECT column1, column2, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
SELECT column1, column2, ...: Specify the columns we want to select from the existing table (oldtable).INTO newtable: Indicate the name of the new table we want to create.[IN externaldb]: Optional. If we want to create the new table in an external database, specify it here.FROM oldtable: Specifying the source table from which we are selecting data.WHERE condition: Optional. Add a condition to filter the rows from the source table before inserting them into the new table.Assume we have a table called products with the following structure:
Table: products
| product_id | product_name | category | price |
|---|---|---|---|
| 1 | Laptop | Electronics | 1200 |
| 2 | Smartphone | Electronics | 800 |
| 3 | Bookshelf | Furniture | 150 |
Now, let’s create a new table named products_copy by copying all columns from the products table:
-- Creating a new table 'products_copy' by copying all columns
SELECT *
INTO products_copy
FROM products;
In this example, the SELECT * statement selects all columns from the products table, and the INTO products_copy statement creates a new table named products_copy based on the result of this selection.
To verify the successful creation of the products_copy table, we can query its contents:
-- Viewing the contents of 'products_copy'
SELECT * FROM products_copy;
Output:
| product_id | product_name | category | price |
|---|---|---|---|
| 1 | Laptop | Electronics | 1200 |
| 2 | Smartphone | Electronics | 800 |
| 3 | Bookshelf | Furniture | 150 |
Let’s start by creating a simple database for our examples. Assume we have a table called employees:
Table: employees
| emp_id | emp_name | emp_salary |
|---|---|---|
| 1 | John Doe | 50000 |
| 2 | Jane Doe | 60000 |
| 3 | Bob Smith | 75000 |
Now, Let’s create a new table named high_earners by selecting employees with salaries greater than $60,000
-- Creating a new table 'high_earners'
SELECT emp_id, emp_name, emp_salary
INTO high_earners
FROM employees
WHERE emp_salary > 60000;
Verify the successful creation of the high_earners table:
-- Viewing the contents of 'high_earners'
SELECT * FROM high_earners;
Output:
| emp_id | emp_name | emp_salary |
|---|---|---|
| 2 | Jane Doe | 60000 |
| 3 | Bob Smith | 75000 |
If we want to create the new table in an external database, we can use the following syntax:
SELECT column1, column2, ...
INTO externaldb.dbo.newtable
FROM oldtable
WHERE condition;