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.

• Restrict Column Data: Rules
  You can create a rule and bind it to a column or user-defined datatype. Rules govern the kind of data that can or cannot be added.
• The NULL Character String
  Only columns for which NULL has been specified in the create table statement and into which you have explicitly entered NULL, or into which no data has been entered can contain null values.
• Insert NULLs into Columns That Do Not Allow Them
  To insert data with select from a table that has null values in some fields into a table that does not allow null values, you must provide a substitute value for any NULL entries in the original table.
• Add Rows Without Values in All Columns
  You can specify values for only some of the columns in a row. Certain rules will apply when adding rows without values for all columns.
• Change a Column’s Value to NULL
  To set a column value to NULL, use the update statement.
• SAP ASE-generated values for IDENTITY columns
  When you insert a row into a table with an IDENTITY column, SAP ASE automatically generates the column value. Do not include the name of the IDENTITY column in the column list or its value in the values list.
• Explicitly Insert Data into an IDENTITY Column
  At times, you may want to insert a specific value into an IDENTITY column. For example, you may want the first row inserted into the table to have an IDENTITY value of 101, rather than 1. Or you may need to reinsert a row that was deleted by mistake.
• Retrieve IDENTITY Column Values with @@identity
  Use the @@identity global variable to retrieve the last value inserted into an IDENTITY column.
• Reserve a Block of IDENTITY Column Values
  The identity grab size configuration parameter allows each SAP ASE process to reserve a block of IDENTITY column values for inserts into tables that have an IDENTITY column.
• Maximum Value of the IDENTITY Column
  The maximum value that you can insert into an IDENTITY column is 10 precision - 1. If you do not specify a precision for the IDENTITY column, SAP ASE uses the default precision for numeric columns (18 digits).