Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to insert records for particular day to another table.
At the same time i want to insert incremental number to another same table w.r.t record
like below
TableA
----------
ColA	ColB	ColDate
101	a	21-Jul-2014
102	b	21-Jul-2014
103	c	22-Jul-2014


After insert
TableB
----------
ColA	RecordNumber	ColB	ColDate
101	1		a	21-Jul-2014
102	2		b	21-Jul-2014
103	3		c	22-Jul-2014
Posted
Updated 21-Jul-14 1:49am
v2

Oracle advise on the best way to achieve this here http://www.oracle-base.com/articles/misc/autonumber-and-identity.php[^]
 
Share this answer
 
v2
1. Before inserting the values in table B, calculate the max number from tableB.

Select Max(RecordNumber)+1 [RecNumber] from tableB

2. In the insert query for tableB use the max number with the other details.
 
Share this answer
 
v3
Comments
CHill60 31-Jul-14 5:45am    
In multi-user environments this can cause issues. It is also not very performant
This will fetch the present auto-increment value.

SELECT IDENT_CURRENT('table_name');

Next auto-increment value.

SELECT IDENT_CURRENT('table_name')+1;
 
Share this answer
 
Comments
vishal_h 22-Jul-14 0:23am    
Thanks for reply. But in Oracle how can it achieved.
use count(column name)+1 in insert statement
 
Share this answer
 
1) You can make that column as identity column
or

2) DECLARE @MAX INT
SELECT @MAX = ISNULL(MAX(FIELD1),1) FROM TABLE1
INSERT INTO TABLE1 ( FIELD1, FIELD2) VALUES (@MAX, 'ABC')
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900