Creating and Updating Tables and Views

Inserting Rows into Tables

Use the INSERT statement to insert data values into tables. The INSERT statement first adds a new row to an existing table, and then inserts the values that you specify into the row. You specify values by using a SET clause or VALUES clause. You can also insert the rows resulting from a query.

Under most conditions, you can insert data into tables through PROC SQL and SAS/ACCESS views. See Updating a View.

Inserting Rows with the SET Clause

With the SET clause, you assign values to columns by name. The columns can appear in any order in the SET clause. The following INSERT statement uses multiple SET clauses to add two rows to NEWCOUNTRIES:

proc sql;
   insert into sql.newcountries
      set name='Bangladesh',
          capital='Dhaka',
          population=126391060
      set name='Japan',
          capital='Tokyo',
          population=126352003;
      
   title "World's Largest Countries";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from sql.newcountries;

Rows Inserted with the SET Clause

                           World's Largest Countries

             Name                  Capital               Population
             ------------------------------------------------------
             Brazil                Brasilia             160,310,357
             China                 Beijing            1,202,215,077
             India                 New Delhi            929,009,120
             Indonesia             Jakarta              202,393,859
             Russia                Moscow               151,089,979
             United States         Washington           263,294,808
             Bangladesh            Dhaka                126,391,060
             Japan                 Tokyo                126,352,003

Note the following features of SET clauses:

As with other SQL clauses, use commas to separate columns. In addition, you must use a semicolon after the last SET clause only.
If you omit data for a column, then the value in that column is a missing value.
To specify that a value is missing, use a blank in single quotation marks for character values and a period for numeric values.

Inserting Rows with the VALUES Clause

With the VALUES clause, you assign values to a column by position. The following INSERT statement uses multiple VALUES clauses to add rows to NEWCOUNTRIES. Recall that NEWCOUNTRIES has six columns, so it is necessary to specify a value or an appropriate missing value for all six columns. See the results of the DESCRIBE TABLE statement in Creating Tables Like an Existing Table for information about the columns of NEWCOUNTRIES.

proc sql;
   insert into sql.newcountries
      values ('Pakistan', 'Islamabad', 123060000, ., ' ', .)
      values ('Nigeria', 'Lagos', 99062000, ., ' ', .); 
   title "World's Largest Countries";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from sql.newcountries;

Rows Inserted with the Values Clause

                           World's Largest Countries

             Name                  Capital               Population
             ------------------------------------------------------
             Brazil                Brasilia             160,310,357
             China                 Beijing            1,202,215,077
             India                 New Delhi            929,009,120
             Indonesia             Jakarta              202,393,859
             Russia                Moscow               151,089,979
             United States         Washington           263,294,808
             Pakistan              Islamabad            123,060,000
             Nigeria               Lagos                 99,062,000

Note the following features of VALUES clauses:

As with other SQL clauses, use commas to separate columns. In addition, you must use a semicolon after the last VALUES clause only.
If you omit data for a column without indicating a missing value, then you receive an error message and the row is not inserted.
To specify that a value is missing, use a space in single quotation marks for character values and a period for numeric values.

Inserting Rows with a Query

You can insert the rows from a query result into a table. The following query returns rows for large countries (over 130 million in population) from the COUNTRIES table. The INSERT statement adds the data to the empty table NEWCOUNTRIES, which was created earlier in Creating Tables Like an Existing Table:

proc sql;
   create table sql.newcountries
      like sql.countries;

proc sql;
   title "World's Largest Countries";
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;

   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from sql.newcountries;

Rows Inserted with a Query

                           World's Largest Countries

             Name                  Capital               Population
             ------------------------------------------------------
             Brazil                Brasilia             160,310,357
             China                 Beijing            1,202,215,077
             India                 New Delhi            929,009,120
             Indonesia             Jakarta              202,393,859
             Russia                Moscow               151,089,979
             United States         Washington           263,294,808

If your query does not return data for every column, then you receive an error message, and the row is not inserted. For more information about how PROC SQL handles errors during data insertions, see Handling Update Errors.

Top of Page