·  SQL INSERT

 

- Info:

  - INSERT statement is used to insert single record into table.

  - String values should be enclosed into single qoutes.

    If inserted value should contain single qoute escape it with another single qoute: 'It''s all right'.

 

- Insert into specific columns:

  - Columns whose values are not specified are set to default values as defined by the table itself.

  - Do not use quotes when defining column names.

    INSERT INTO PEOPLE(NAME     , WEIGHT)

    VALUES            ('Johnson', 150   );

 

- Insert into columns in the order they appear:

  - If you ommit column names, values are inserted into columns as they come in table from left to right.

  - You need to suply ALL the values even if columns have their default vaules.

    INSERT INTO PEOPLE

    VALUES            ('Johnson', 45, SYSDATE-45, 150, 1);

 

- Use variables to be prompted for values while executing script:

  - This works in TOAD.

    INSERT INTO PEOPLE(NAME , AGE)

    VALUES            (&name, &age);

 

- Use subquery to define values:

  - Match the number of columns in INSERT clause to those in subquery.

    INSERT INTO PEOPLE(NAME, AGE)

      (

        SELECT EMPLOYEE, AGE

        FROM   EMPLOYEES

        WHERE  JOB_ID LIKE '%IT%'

      );