Chapter 15 – Data Manipulation Language (DML)
“I tried to draw people more realistically, but the figure I neglected to update was myself.”
- Joe Sacco
INSERT Syntax # 1
The following syntax of the INSERT does not use the column names as part of the command. Therefore, it requires that the VALUES portion of the INSERT match each and every column in the table with a data value or a NULL.
INSERT [ INTO ] <table-name> VALUES
( <literal-data-value> [ ...,<literal-data-value> ] ;
The INSERT statement is used to put new row(s) into a table. A status is the only returned value from the database. No rows are returned to the user. This INSERT syntax requires either a data value or a NULL for all the columns in a table. When executed, this code places a single new row into a table.
INSERT example with Syntax 1
After the execution of the above INSERT, there is a new row with the integer value of 1 going into Column1, the integer value of 5 going into Column2, the character value of Jones going into Column3, a NULL value going into Column4 , and an integer value of 45000 going into Column5. The NULL expressed in the VALUES list means no data.
INSERT Syntax # 2
The syntax of the second type of INSERT follows:
INSERT [ INTO ] <table-name>
( <column-name> [...,<column-name> ]
VALUES
( <literal-data-value> [...,<literal-data-value> ] ;
This is another form of the INSERT statement that can be used when some of the data is not available. It allows for the missing values (NULL) to be eliminated from the list in the VALUES clause. It is also the best format when the data is arranged in a different sequence than the CREATE TABLE.
INSERT example with Syntax 2
The second INSERT statement provides two advantages. First, notice that the column names Last_Name and First_Name have been switched to match the data values so we supplied the order of columns in the file. Also, notice that Dept_No does not appear in the column list, therefore, it is assumed to be NULL. This makes all entries NULL for Dept_No. This is a good format to use when the data is coming from a file and does not match the order of the table columns.
INSERT/SELECT Command
The syntax of the INSERT / SELECT is:
INSERT [ INTO ] <table-name>
SELECT <column-name> [...,<column-name> ]
FROM <table-name> ;
Although the INSERT is great for adding a single row not currently present in the system, an INSERT/SELECT is even better when the data already exists within Hadoop. In this case, the INSERT is combined with a SELECT. However, no rows are returned to the user. Instead, they go into the table as new rows.
The SELECT reads the data values from the one or more columns in one or more tables and uses them as the values to INSERT into another table. Simply put, the SELECT takes the place of the VALUES portion of the INSERT.
This is a common technique for building data marts, interim tables and temporary tables. It is normally a better and much faster alternative than extracting the rows to a data file, then reading the data file and inserting the rows using a utility.
INSERT/SELECT example using All Columns (*)
Use the above example when all columns are desired and you want to make an exact copy of the second table. Both tables must have the exact same number of columns in the exact same order with the exact same data types.
INSERT/SELECT example with Less Columns
In both of the above example, only the specified columns are populated in Employee_Table2. Salary will contain NULL values for each row inserted.
DELETE and TRUNCATE Examples
DELETE FROM Employee_Table4 ;
TRUNCATE Table Employee_Table4 ;
The top example above will delete all the rows in the table. The second example will only delete the rows that match the criteria. Use the TRUNCATE command to quickly remove all rows in a table.