I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.
CREATE TABLE myTable99(Col1 int IDENTITY(100,1), Col2 varchar(25))
INSERT INTO myTable99(Col2)
SELECT 'Brett' UNION ALL
SELECT 'Pat' UNION ALL
SELECT * FROM myTable99
DROP TABLE myTable99
I agree with Pat. Here is an exerp from the Create Table subject in Books Online for SQL:
Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).