The situation is that, when I import a file into the database, one of the first thing I usually do is to assign an unique ID for each record.
I normally do below in TSQL
ALTER TABLE MyTable
ADD ID INT IDENTITY(1,1)
I am wondering if there is something similar in PL SQL?
All my search result come back with multiple steps.
Then I'd like to know what PL SQL programmer typically do to ID records after importing a file. Do they do that? The main purpose for me to ID these records is to trace it back after manipulation/copying.
Again, I understand there is solution there, my further question is whether PL SQL programmer actually do that, or there is other alternative which making this step not necessary in PL SQL?
The answer given by @Littlefoot would be my recommendation too - but still I thought I could mention the following variant which will work only if you do not intend to add more rows to the table later.
ALTER TABLE MyTable add id number(38,0);
update MyTable set id = rownum;
commit;
My test:
SQL> create table tst as select * from all_tables;
Table created.
SQL> alter table tst add id number(38,0);
Table altered.
SQL> update tst set id = rownum;
3815 rows updated.
SQL> alter table tst add constraint tstPk primary key (id);
Table altered.
SQL>
SQL> select id from tst where id < 15;
ID
----------
1
2
3
4
5
6
7
8
9
10
11
ID
----------
12
13
14
14 rows selected.
But as mentioned initially,- this only fixes numbering for the rows you have at the time of the update - your'e not going to get new id values for new rows anytime later - if you need that, go for the sequence solution.