July 29, 2003 at 10:43 am
hi there everyone,
i want to add a column to the target table after Dts transformation ..and i want the column to keep a record of the number of values in the column in increasing order..i mean i want to some kind of autonumber thing as we have in Microsoft access.
how can i do this ..could anyone guide me please..with a sample..or guide me how to write a code or anything tht would help.
thnx
July 29, 2003 at 2:42 pm
It sounds like you would be wanting an identity column. (sample table below)
create table tbl_a (
gen_id int IDENTITY (1, 1) NOT NULL ,
field_x char (4) not null
) on primary
You need to set the identity column to a starting value with the reseed command.
dbcc checkident(tbl_a, reseed,1)
Once you have the identity column defined and reseeded to 1 then every record you insert into the table will get an increment of 1 in the indentity column. There is no need for you to map anything to it in the translation.
If you delete or truncate all of the records out of the table you will have to rerun the reseed command to start back at 1 otherwise it will just continue on.
Here are the permissions required to do the reseed (from SQL2000 help)
DBCC CHECKIDENT permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database role, and are not transferable.
Hope that helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply