Inserting Data Using SELECT

Up to this point, all of our INSERT statements have inserted a single row. Each INSERT contained a VALUES clause listing the constants to be inserted. Another form of the INSERT statement also exists; it allows the output of a SELECT to be used to insert values into a table.

Suppose we wish to add all of our friends from the friend table to the customer table. As shown in Figure , instead of a VALUES clause, INSERT can use the output of SELECT to insert data into the table.  

        test=> INSERT INTO customer (name, city, state, country)
        test-> SELECT trim(firstname) || ' ' || lastname, city, state, 'USA'
        test-> FROM friend;
        INSERT 0 6  

Each column of the SELECT matches a receiving column in the INSERT. Column names and character string constants can be used in the SELECT output. In the line INSERT 0 6, six rows are inserted into the customer table. A zero object identifier is returned because more than one row is inserted.

Inserting into the customer name column presents an interesting challenge. The friend table stores first and last names in separate columns. In contrast, the customer table has only a single name column. The solution is to combine the firstname and lastname columns, with a space separating them. For example, a firstname of 'Dean' and a lastname of 'Yeager' must be inserted into customer.name as 'Dean Yeager'. This combination becomes possible with trim() and the || operator. The trim() function removes trailing spaces. The two pipe symbols, ||, allow character strings to be joined together to form a single string, in a process called concatenation. In this example, trim(firstname), space (' '), and lastname are joined using ||.