Transact-SQL User's Guide

---

Inserting 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 especially want to use this form of the insert command to insert all of the values in a row except the text 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 

 

Adaptive Server 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.

Restricting 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 pub_id column of the publishers table is an example. A rule called pub_idrule, which specifies acceptable publisher identification numbers, is bound to the column. The acceptable IDs are "1389", "0736", "0877", "1622", and "1756" or any four-digit number beginning with "99". If you enter any other number, you get an error message.

When you get this kind of error message, you may want to use sp_helptext to look at the definition of the rule: :

sp_helptext pub_idrule 

---------
1
(1 row affected)
text
---------------------------------------------------
create rule pub_idrule
as @pub_id in ("1389", "0736", "0877", "1622", "1756")
or @pub_id like "99[0-9][0-9]"
(1 row affected)

 

For more general information on a specific rule, use sp_help. Or use sp_help with a table name as a parameter to find out if any of the columns has a rule. See Chapter 12, "Defining Defaults and Rules for Data."

Using the NULL character string

Only columns for which NULL was specified in the create table statement and into which you have explicitly entered NULL (no quotes), or into which no data has been entered, contain null values. Avoid entering the character string "NULL" (with quotes) as data for a character column. It can only lead to confusion. Use "N/A" or "none" or a similar value instead. When you want to enter the value NULL explicitly, do not use single or double quotes.

To explicitly insert NULL into a column:

values({expression | null}
[, {expression | null}]...)

 

The following example shows two equivalent insert statements. In the first statement, the user explicitly inserts a NULL into column t1. In the second, Adaptive Server provides a NULL value for t1because the user has not specified an explicit column value:

create table test
(t1 char(10) null, t2 char(10) not null) 

insert test
values (null, "stuff") 

insert test (t2)
values ("stuff") 

 

NULL is not an empty string

The empty string (" "or ' ') is always stored as a single space in variables and column data. This concatenation statement is equivalent to "abc def", not "abcdef":

"abc" + "" + "def" 

 

The empty string is never evaluated as NULL.

Inserting 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. For example, to insert data into an advances table that does not allow null values, this example substitutes "0" for the NULL fields:

insert advances
select pub_id, isnull(advance, 0) from titles

 

Without the isnull function, this command inserts all the rows with non-null values into advances and produces error messages for all the rows where the advance column in titles contains NULL.

If you cannot make this kind of substitution for your data, you cannot insert data containing null values into columns with a NOT NULL specification.

Adding rows without values in all columns

When you specify values for only some of the columns in a row, one of four things can happen to the columns with no values:

• If a default value exists for the column or user-defined datatype of the column it is entered. See Chapter 12, "Defining Defaults and Rules for Data," or create default in the Reference Manual for details.

• If NULL was specified for the column when the table was created and no default value exists for the column or datatype, NULL is entered. See also create table in the Reference Manual.

• If the column has the IDENTITY property, a unique, sequential value is entered.

• If NULL was not specified for the column when the table was created and no default exists, Adaptive Server rejects the row and displays an error message.

 

Table 8-4 shows what you would see under these circumstances:

Columns with no values

Default Exists for Column or Datatype	Column Defined NOT NULL	Column Defined to Allow NULL	Column Is IDENTITY
Yes	The default	The default	Next sequential value
No	Error message	NULL	Next sequential value

You can use sp_help to get a report on a specified table or default or on any other object listed in the system table sysobjects. To see the definition of a default, use sp_helptext.

Changing a column's value to NULL

To set a column value to NULL, use the update statement:

set column_name = {expression | null}
        [, column_name = {expression | null}]...

 

For example, to find all rows in which the title_id is TC3218 and replace the advance with NULL:

update titles
        set advance = null
        where title_id = "TC3218" 

 

Adaptive Server-generated values for IDENTITY columns

When you insert a row into a table with an IDENTITY column, Adaptive Server 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.

This insert statement adds a new row to the sales_daily table. Notice that the column list does not include the IDENTITY column, row_id:

insert sales_daily (stor_id)
        values ("7896")

 

The following statement shows the row that was added to sales_daily. Adaptive Server automatically generated the next sequential value, 2, for row_id:

select * from sales_daily
        where stor_id = "7896"

sale_id      stor_id
        -------      -------
        1      7896
        (1 row affected)

 

Explicitly inserting data into an IDENTITY column

At times, you may want to insert a specific value into an IDENTITY column, rather than accept a server-generated value. 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.

The table owner can explicitly insert a value into an IDENTITY column. The Database Owner and System Administrator can explicitly insert a value into an IDENTITY column if they have been granted explicit permission by the table owner or if they are acting as the table owner through the setuser command.

Before inserting the data, set the identity_insert option on for the table. You can set identity_insert on for only one table at a time in a database within a session.

This example specifies a "seed" value of 101 for the IDENTITY column:

set identity_insert sales_daily on

insert sales_daily (syb_identity, stor_id)
        values (101, "1349")

 

The insert statement lists each column, including the IDENTITY column, for which a value is specified. When the identity_insert option is set to on, each insert statement for the table must specify an explicit column list. The values list must specify an IDENTITY column value, since IDENTITY columns do not allow null values.

After you set identity_insert off, you can insert IDENTITY column values automatically, without specifying the IDENTITY column, as before. Subsequent insertions use IDENTITY values based on the value explicitly specified after you set identity_insert on. For example, if you specify 101 for the IDENTITY column, subsequent insertions would be 102, 103, and so on.

Adaptive Server does not enforce the uniqueness of the inserted value. You can specify any positive integer within the range allowed by the column's declared precision. To ensure that only unique column values are accepted, create a unique index on the IDENTITY column before inserting any rows.

Retrieving IDENTITY column values with @@identity

Use the @@identity global variable to retrieve the last value inserted into an IDENTITY column. The value of @@identity changes each time an insert, select into, or bcp statement attempts to insert a row into a table. @@identity does not revert to its previous value if the insert, select into, or bcp statement fails or if the transaction that contains it is rolled back. If the statement affects a table without an IDENTITY column, @@identity is set to 0.

• If the statement inserts multiple rows, @@identity reflects the last value inserted into the IDENTITY column.

 

The value for @@identity within a stored procedure or trigger does not affect the value outside the stored procedure or trigger. For example:

select @@identity

---------------------------------------
        101

create procedure reset_id as
        set identity_insert sales_daily on
        insert into sales_daily (syb_identity, stor_id)
        values (102, "1349")
        select @@identity
        select @@identity

execute reset_id

---------------------------------------
        102

select @@identity

---------------------------------------
        101

 

Reserving a block of IDENTITY column values

The identity grab size configuration parameter allows each Adaptive Server process to reserve a block of IDENTITY column values for inserts into tables that have an IDENTITY column. This configuration parameter reduces the number of times an Adaptive Server engine must hold an internal synchronization structure when inserting implicit identity values. For example, to set the number of reserved values to 20:

sp_configure "identity grab size", 20

 

When a user performs an insert into a table containing an IDENTITY column, Adaptive Server reserves a block of 20 IDENTITY column values for that user. Therefore, during the current session, the next 20 rows the user inserts into the table will have sequential IDENTITY column values. If a second user inserts rows into the same table while the first user is performing inserts, Adaptive Server will reserve the next block of 20 IDENTITY column values for the second user.

For example, suppose the following table containing an IDENTITY column has been created and the identity grab size is set to 10:

create table my_titles
        (title_id   numeric(5,0)    identity,
        title          varchar(30)     not null)

 

User 1 inserts these rows into the my_titles table:

insert my_titles (title)
        values ("The Trauma of the Inner Child")

insert my_titles (title)
        values ("A Farewell to Angst")

insert my_titles (title)
        values ("Life Without Anger")

 

Adaptive Server allows user 1 a block of 10 sequential IDENTITY values, for example, title_id numbers 1-10.

While user 1 is inserting rows to my_titles, user 2 begins inserting rows into my_titles. Adaptive Server grants user 2 the next available block of reserved IDENTITY values, that is, values 11-20.

If user 1 enters only three titles and then logs off Adaptive Server, the remaining seven reserved IDENTITY values are lost. The result is a gap in the table's IDENTITY values. Avoid setting the identity grab size too high, because this can cause gaps in the IDENTITY column numbering.

Reaching the IDENTITY column's maximum value

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, Adaptive Server uses the default precision (18 digits) for numeric columns.

Once an IDENTITY column reaches its maximum value, insert statements return an error that aborts the current transaction. When this happens, use one of the following methods to remedy the problem.

Create a new table with a larger precision

If the table contains IDENTITY columns that are used for referential integrity, you need to retain the current numbers for the IDENTITY column values.

1. Use create table to create a new table that is identical to the old one except with a larger precision value for the IDENTITY column.

2. Use insert into to copy the data from the old table into to the new one.

 

Renumber the table's IDENTITY columns with bcp

If the table does not contain IDENTITY columns used for referential integrity, and if there are gaps in the numbering sequence, you can renumber the IDENTITY column to eliminate gaps, which allows more room for insertions.

To sequentially renumber IDENTITY column values and remove the gaps, use the bcp utility:

1. From the operating system command line, use bcp to copy out the data. For example:

   bcp pubs2..mytitles out mytitles_file -N -c

    

   The -N instructs bcp not to copy the IDENTITY column values from the table to the host file. The -c instructs bcp to use character mode.

2. In Adaptive Server, create a new table that is identical to the old table.

3. From the operating system command line, use bcp to copy the data into the new table:

   bcp pubs2..mynewtitles in mytitles_file -N -c

    

   The -N instructs bcp to have Adaptive Server assign the IDENTITY column values when loading data from the host file. The -c instructs bcp to use character mode.

4. In Adaptive Server, drop the old table, and use sp_rename to change the new table name to the old table name.

 

If the IDENTITY column is a primary key for joins, you may need to update the foreign keys in other tables.

By default, when you bulk copy data into a table with an IDENTITY column, bcp assigns each row a temporary IDENTITY column value of 0. As it inserts each row into the table, the server assigns it a unique, sequential IDENTITY column value, beginning with the next available value. To enter an explicit IDENTITY column value for each row, specify the -E (UNIX) or /identity (OpenVMS) flag. Refer to the Utility Guide for your platform for more information on bcp options that affect IDENTITY columns.

---