Home > Articles > Data > SQL

  • Print
  • + Share This
This chapter is from the book

Creating Copies of Data

In this chapter we stressed that views are not copies of data but rather virtual tables with no physical data associated with them. If an independent copy of data is what you want, check to see what your system provides. At a minimum, you can create a new table and then INSERT data from an existing table (your system may use a different datatype for price).

SQL
create table bizprices
(title_id   char(6)     not null ,
price     numeric(8,2)   null)
[table created]

insert into bizprices
select title_id, price
from titles 
where type = 'business'
[4 rows]

select *
from bizprices
title_id price
======== ==========
BU1032        29.99
BU1111        21.95
BU2075        12.99
BU7832        29.99
[4 rows]

This technique is useful for creating test tables, new tables that resemble existing tables, and tables that have some or all of the columns in several other tables. However, it introduces redundancy: Now you have additional copies of title numbers and their prices. As you change values in the original table, values in the new table will not change, unless you add a REFERENCES constraint or the like.

SQL Variants

Some systems offer SQL extensions that allow you to define a table and put data into it (based on existing definitions and data) without going through the usual data definition process.

In Transact-SQL's version, the new table (named in the INTO clause) holds data defined by the columns you specify in the SELECT list, the table(s) you name in the FROM clause, and the rows you choose in the WHERE clause.

Transact-SQL
select title_id, price
into bizprices
from titles
where type = 'business'

Oracle adds an AS and a SELECT statement to CREATE TABLE.

Oracle
SQL> create table bizprices
 2  as
 3  select title_id, price
 4  from titles
 5  where type = 'business';

In either case, the data look like this:

Oracle
TITLE_ PRICE
------ ---------
BU1032     29.99
BU1111     21.95
BU2075     12.99
BU7832     29.99
  • + Share This
  • 🔖 Save To Your Account