add row no automatically

Jane · June 13th, 2004, 07:27 PM

hi,

how am i add a row no. on my table to the record automatically using a SQL command?

eg

table

row# cust no value
1 a0010 30000
2 a0001 15000
3 b0002 2000

om_prakash · June 13th, 2004, 11:07 PM

If you are using SQL, then you can set this field as identity. By setting this as identity, new Id will be generated automatically, when new row is created.

Om Prakash

Jane · June 23rd, 2004, 06:57 PM

WHAT R THE COMMAND USING SQL TO ADDING ROW?

Steven · June 23rd, 2004, 07:23 PM

You're not just after:

Code:

INSERT INTO TableName (IntegerField, TextField) VALUES (1, 'Text')

are you?

I am a loud man with a very large hat. This means I am in charge

happygv · June 24th, 2004, 07:29 AM

Hi Jane,

You can use this, if that ROWNUMBER column is not set as Identity.

Code:

Declare @newRowNo int
select @newRowNo = coalesce(max(RowNo)+1,1) from YOURTABLENAME
insert YOURTABLENAME values(@newRowNo, 'a0010', 30000)

Everytime you run this
select coalesce(max(RowNo)+1,1) from YOURTABLENAME
You get the new incremented row number which you can use for next INSERT.

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

Jane · June 24th, 2004, 07:42 PM

yes, i can get it where i use 'INSERT' COMMAND BUT FROM MY SCENARIO, I ALREADY HAVE A DATA IN MY TABLE, JUST THAT WANT TO ADD THE ROW NO INTO IT?

happygv · June 25th, 2004, 05:02 AM

Still not sure if you are using ACCESS or SQL SERVER. Let me know so that I can give you the proper approach to put values into rownum column.

_________________________
-Vijay G

Strive for Perfection

Jane · June 27th, 2004, 02:40 AM

i using SQL Server,
and my table as this

rowno item_no value
null a0100 10000
null a0101 30000
null b0100 20000

i need to add a row no according to the value ascending.

planoie · June 27th, 2004, 01:58 PM

Is this something you are writing into a program or are you just modifying data?

happygv · June 28th, 2004, 02:11 AM

Hi Jane,

Is this what you are looking for?

Code:

Declare @ctr int
Set @ctr=1
Declare @Itemno varchar(10)

Declare Update_Rownum cursor For
Select Item_no from <yourTable> order by Item_no

Open Update_Rownum
Fetch next from Update_Rownum into @Itemno
while @@Fetch_Status=0 
begin
     Update <yourTable> set rowno=@ctr where Item_no = @Itemno
     Set @ctr=@ctr+1
    Fetch next from Update_Rownum into @Itemno
end
Close Update_Rownum
Deallocate Update_Rownum

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection