How to Insert Each Type of Data

Data types are inserted in different ways. Table 9.2 describeshow to insert each data type and what to watch out for.

SELECT INTO and INSERT… SELECT

There are two useful commands that will copy data from one tableto another. The first, SELECT INTO, creates a new tablebased on rows in an existing table.

select *
into CA_authors
from authors
where state  =  ‘CA’

(15 row(s) affected)

All authors in the authors table who live in Californiawere copied to a new table, CA_authors.

You may select specific columns using SELECT INTO tocreate a table with fewer fields than the original or join tablestogether. Virtually any query that is possible with SELECTis possible with SELECT INTO:

select a.au_lname, t.title, t.price
into   whowrotewhat
from   authors a join titleauthor ta on a.au_id = ta.au_id
       join titles t on t.title_id = ta.title_id

---

NOTE:  SELECT INTO is a nonlogged operation. Itrequires that a special option, SELECT INTO/BULKCOPY, beset on in the database.

---

---

WARNING:  After performing a SELECT INTO ora BULKCOPY operation (usually with the bcp utility in\mssql\binn) you will not be able to perform anincremental backup of the database until you first perform a fullbackup.

If you are using incremental backups on your database, youprobably should not enable the SELECT INTO/BULKCOPYoption in that database.

---

INSERT… SELECT is a type of INSERTstatement. You specify a regular INSERT statement, but donot specify a values clause. Instead, you provide a query thatreturns values to be inserted. INSERT... SELECT willselect rows from an existing table and insert those rows in anotherexisting table. If you had created the California authors table andwanted to also add in the authors from Maryland, this query woulddo the trick:

insert CA_authors
select * from  authors
where state =  ‘MD’

(2 row(s) affected)

This query copies rows between two existing tables.

You can select specific columns by restricting the column list(the SELECT list) and specific rows by restricting the WHERE list.

---

TIP:  If you want to copy all rows in your table back toitself, for instance, or if you wanted to create a lot of test datavery quickly, you could perform an INSERT… SELECT onthe same table:

insert testdata
select * from testdata

---

---

NOTE:  Differences between INSERT... SELECT and SELECT... INTO

INSERT... SELECT is a logged operation; SELECT...INTO is nonlogged. SELECT... INTO creates a new tableout of the selected data. INSERT... SELECT inserts datainto an existing table.

---