Today someone asked me how to place a sequential number as a primary key value in a table. He did not want to write a loop in his procedure to increment the primary key value, just a 'plain and simple insert statement' with the table doing the calculations.
In Oracle, the SEQUENCE is the object place holder to use on a table trigger event.
In MSSQL, INCREMENT is the property and can be used as follows:
-------------------------------------------------------------------------
/*
To test this, first set up your table, with the IDENTITY property,
followed with two variables (starting int , increment by value),
in this example the ProductID starts at one, and every new record
will increase the Product counter ID by 1.
*/
CREATE TABLE product
(productID INT IDENTITY(1,1) ,
productDescription VARCHAR(20),
CONSTRAINT ID_PK PRIMARY KEY (productID)
)
/*
Insert some data into the table
*/
insert into product (productDescription) values ('bicycles')
insert into product (productDescription) values ('cars')
insert into product (productDescription) values ('trains')
insert into product (productDescription) values ('buses')
insert into product (productDescription) values ('trams')
/*
Select the data, to test the increment
*/
select * from product
/*
Drop test table
*/
drop table product
-------------------------------------------------------------------------
I hope this article can you, and as always I welcome feedback on this article.
