- With a View Toward Flexibility
- View Commands
- Advantages of Views
- How Views Work
- Data Modification Through Views
- Creating Copies of Data
- Summary
This chapter is from the book
This chapter is from the book
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