SQL Copy

SQL Copy

This tutorial covers SQL Copy.

Here we explore the concept of copying data from one table to another.

There are two methods of copying data in SQL

This page encompasses:

• select into
• insert into select

Here we use two tables. First is the table “student_info” from the previous tutorials, and the second is a table named “grade“, linked to the first table with a foreign key.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Patrick	Dumont	21

id	subjectName	grade	passed	studentID
1	History	C	1	1
2	Physics	A	1	2
3	History	F	0	3
4	Biology	A	1	NULL
5	Biology	E	1	5

Select Into

The “select into” clause copies data from an existing table through a query.

The query then inserts the data to a new table. The new table mirrors the column names as well as data types of the original table.

Below is an example.

SELECT * INTO new_table
FROM student_info;

5 rows affected. Now let’s check the new table with the “SELECT * FROM sys.tables;” query. We can also display the result of the new table with the “SELECT * FROM new_table;” statement.

We can also copy only specific columns or records.

For instance.

SELECT firstName, lastName INTO new_copy
FROM student_info
WHERE age BETWEEN 15 AND 25;

3 rows affected.

Insert Into Select

The “insert into select” clause copies data from one table and adds it to another.

Essentially, the query transfers data from an existing table to another existing table. The data types must be identical.

Below is an example

INSERT INTO grade (subjectName)
SELECT firstName FROM student_info;

5 rows affected. We have inserted new data into the “grade” table. So let’s check the outcome.

id	subjectName	grade	passed	studentID
1	History	C	1	1
2	Physics	A	1	2
3	History	F	0	3
4	Biology	A	1	NULL
5	Biology	E	1	5
6	John	NULL	NULL	NULL
7	Patrick	NULL	NULL	NULL
8	Sam	NULL	NULL	NULL
9	Mike	NULL	NULL	NULL
10	Patrick	NULL	NULL	NULL

---

Previous: SQL Procedure