Insert Data into Specific Columns

You can add data to some columns in a row by specifying only those columns and their data. All other columns that are not included in the column list must be defined to allow null values. The skipped columns can accept defaults. If you skip a column that has a default bound to it, the default is used.

You may want to use this form of the insert command to insert all of the values in a row except the text, unitext, or image values, and then use writetext to insert the long data values so that these values are not stored in the transaction log. You can also use this form of the command to skip over timestamp data.

Adding data in only two columns, for example, pub_id and pub_name, requires a command like this:

insert into publishers (pub_id, pub_name) 
values ("1756", "The Health Center")

The order in which you list the column names must match the order in which you list the values. The following example produces the same results as the previous one:

insert publishers (pub_name, pub_id) 
values("The Health Center", "1756")

Either of the insert statements places “1756” in the identification number column and “The Health Center” in the publisher name column. Since the pub_id column in publishers has a unique index, you cannot execute both of these insert statements; the second attempt to insert a pub_id value of “1756” produces an error message.

The following select statement shows the row that was added to publishers:

select * 
from publishers 
where pub_name = "The Health Center"
pub_id  pub_name             city    state 
------- -----------------    ------  ------- 
1756    The Health Center    NULL    NULL 

SAP ASE enters null values in the city and state columns because no value was given for these columns in the insert statement, and the publisher table allows null values in these columns.