Increment a field by 10

[brialex]

I am trying to find a way to increment a field by 10 for each new record that is inserted into my table. The current value of the field is 00000000. I would like each record after the original to increment by 10 when added such as 00000010,00000020,00000030.... Can anyone show me how to do this? Thanks!

 

[SQLDenis]

set up identity with a increment of 10

example

create table testInc (id int identity(10,10))

insert testInc default values
insert testInc default values
insert testInc default values
insert testInc default values

select * from testInc

10
20
30
40

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[brialex]

How would apply that is this scenario:

I want to insert approx. 3900 records into table A but I have to look at table B in order to make sure I am pulling the correct records. For example:

insert into cpnpmi_t (pmo_cde,itm_num,bil_cur,pmo_chc,pmo_cde1,seq_nbr8,itm_num1,
sub_chc)

SELECT 'MMECW7C2',I.ITM_NUM,'US$',' ','MMECW7C2',00000000,I.ITM_NUM,' '
FROM CDSITM_M I
WHERE I.ITM_NUM <> '1281' AND ITS_CDE = 'SA'

The seq_nbr8 field is the one that I want to increment.

Thanks!

 

[SQLDenis]

Do a running count

example

CREATE TABLE Rankings (Value Char(1)) 
INSERT INTO Rankings 
SELECT 'A' UNION ALL 
SELECT 'B' UNION ALL 
SELECT 'C' UNION ALL 
SELECT 'D' UNION ALL 
SELECT 'E' UNION ALL 
SELECT 'F' 


select *,(select count(*) * 10 from Rankings r where r.value <=r2.Value )
from Rankings r2


A	10
B	20
C	30
D	40
E	50
F	60

in your case you need to have a unique id or value in your table

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[brialex]

Thanks Denis!