To pull values into a table from one or more other tables, you can use a SELECT clause in the INSERT statement. The select clause can insert values into some or all of the columns in a row.
Inserting values for only some columns can come in handy when you want to take some values from an existing table. Then, you can use update to add the values for the other columns.
Before inserting values for some, but not all, columns in a table, make sure that either a default exists, or you specify NULL for the columns for which you are not inserting values. Otherwise, an error appears.
When you insert rows from one table into another, the two tables must have compatible structures—that is, the matching columns must be either the same data types or data types between which SQL Anywhere automatically converts.
If the columns are in the same order in their CREATE TABLE statements, you do not need to specify column names in either table. Suppose you have a table named NewProducts that contains some rows of product information in the same format as in the Products table. To add to Products all the rows in NewProducts:
INSERT Products SELECT * FROM NewProducts;
You can use expressions in a SELECT statement inside an INSERT statement.
You can use the SELECT statement to add data to some, but not all, columns in a row just as you do with the VALUES clause. Simply specify the columns to which you want to add data in the INSERT clause.
You can insert data into a table based on other data in the same table. Essentially, this means copying all or part of a row.
For example, you can insert new products, based on existing products, into the Products table. The following statement adds new Extra Large Tee Shirts (of Tank Top, V-neck, and Crew Neck varieties) into the Products table. The identification number is 30 greater than the existing sized shirt:
INSERT INTO Products SELECT ID + 30, Name, Description, 'Extra large', Color, 50, UnitPrice, NULL FROM Products WHERE Name = 'Tee Shirt';