CC SQL 19-insert Data

Card Set Information

Author:
dau108
ID:
146377
Filename:
CC SQL 19-insert Data
Updated:
2012-04-08 11:40:28
Tags:
CC SQL 19 insert Data
Folders:

Description:
CC SQL 19-insert Data
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user dau108 on FreezingBlue Flashcards. What would you like to do?


  1. understanding Data insertion
    As its name suggests, INSERT is used to insert (add) rows to a database table.INSERT can be used in several ways:

    • 1. To insert a single complete row
    • 2. To insert a single partial row
    • 3. To insert multiple rows
    • 4. To insert the results of a query
  2. inserting complete row
    • Input

    • INSERT INTO Customers
    • VALUES(NULL,
    • 'Pep E. LaPew',
    • '100 Main Street',
    • 'Los Angeles',
    • 'CA',
    • '90046',
    • 'USA',
    • NULL,
    • NULL);

    The safer (and unfortunately more cumbersome) way to write the INSERT statement is as follows:

    • Input

    • INSERT INTO customers(cust_name,
    • cust_address,
    • cust_city,
    • cust_state,
    • cust_zip,
    • cust_country,
    • cust_contact,
    • cust_email)
    • VALUES('Pep E. LaPew',
    • '100 Main Street',
    • 'Los Angeles',
    • 'CA',
    • '90046',
    • 'USA',
    • NULL,
    • NULL);
  3. inserting multiple rows
    • Input

    • INSERT INTO customers(cust_name,
    • cust_address,
    • cust_city,
    • cust_state,
    • cust_zip,
    • cust_country)
    • VALUES(
    • 'Pep E. LaPew',
    • '100 Main Street',
    • 'Los Angeles',
    • 'CA',
    • '90046',
    • 'USA'
    • ),
    • (
    • 'M. Martian',
    • '42 Galaxy Way',
    • 'New York',
    • 'NY',
    • '11213',
    • 'USA'
    • );

    • Analysis

    Here a single INSERT statement has multiple sets of values, each enclosed within parentheses, and separated by commas.
  4. inserting retrieved data
    • Input

    • INSERT INTO customers(cust_id,
    • cust_contact,
    • cust_email,
    • cust_name,
    • cust_address,
    • cust_city,
    • cust_state,
    • cust_zip,
    • cust_country)
    • SELECT cust_id,
    • cust_contact,
    • cust_email,
    • cust_name,
    • cust_address,
    • cust_city,
    • cust_state,
    • cust_zip,
    • cust_country
    • FROM custnew;

    • Analysis

    This example uses INSERT SELECT to import all the data from custnew intocustomers. Instead of listing the VALUES to be inserted, the SELECT statement retrieves them from custnew. Each column in the SELECT corresponds to a column in the specified columns list. How many rows will this statement insert? That depends on how many rows are in the custnew table. If the table is empty, no rows will be inserted (and no error will be generated because the operation is still valid). If the table does, in fact, contain data, all that data is inserted into customers.

What would you like to do?

Home > Flashcards > Print Preview