SQL Server: auto-increment fields separately
I have a table with two columns where none of the columns are unique. I need to auto-grow a column number
separately for each user.
user | number
1 | 1
2 | 1
1 | 2
3 | 1
The only idea I could come up with would be to find the last used number and manually increment it by one. Is there a more efficient way?
source to share
Instead of a field, number
you can create a field auto increment
in the table (I call it id
) and get what you want number
with a query:
first adding id:
alter table table_name add id int not null IDENTITY(1,1)
you don't need a field with numbers anymore:
alter table table_name drop column number
Request to get number
(you can use it to create a view):
select user,
row_number() over(partition by user order by id) as number
from table_name
source to share
Find the user and enlarge it.
INSERT INTO YOUR_TABLE (user, number)
SELECT (MAX(number) + 1)
FROM YOUR_TABLE
WHERE user = USER_ID
The term auto-increment only covers auto-incrementing a primary key . See this page for details .
source to share
You can do it in two queries:
userno - the user ID in general that you want to insert
first write (query 1) if you are already in the table:
insert into table_name
select userno,count(*) from table_name where user = userno
group by user;
if it returns an empty string, then it just writes (query 2), that is, a new user should be inserted:
insert into table_name values(userno,1);
source to share