Auto increment column in table based on another column value

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I need result like this:

ChNo====|=====Row Column
  11     |     10
  12     |     14
  13     |     16
  15     |     18
  17     |     19
  20     |     21

Row Column =>Will Generate Num based on ChNo Column (exists or not)

SQL

if not exists(select * from TableNameA where ChNo = 10)

if the given number not exists in the TableNameA(ChNo) column, i need to Auto Increment (Row Column) from 10.

The num 11 exists in ChNo Column, so i want to increment until it doesn't exists in that ChNo column.

Please give me a solution
Thanks.

Posted 6-Oct-15 20:58pm

Sathish km

Updated 12-Oct-15 5:47am

Maciej Los

v2

Add a Solution

Comments

Tomas Takac 7-Oct-15 3:25am

I'm confused. Are "ChNo" and "Row Column" in the same table?

Sathish km 7-Oct-15 4:36am

yes.

Tomas Takac 7-Oct-15 5:00am

How is ChNo generated? I guess this happens in your application, isn't it? Then you should generate the other number in the same place I guess.

Sathish km 7-Oct-15 5:20am

ChNo IS GENERATED BY starting input number given to textbox(UI). Only we have to the given textbox num which is already exist in table or not. If it exists means skip it and increment it to check whether it is available. Each and Every num i have to check.

Sathish km 7-Oct-15 5:20am

give any solution for this

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2015-10-12T05:18:00

Solution 1

What you appear to be trying to do is "find a gap in a sequence" using SQL.

If I replicate your data as

SQL

create table temp
(
    ChNo int
)

insert into temp values (11),(12),(13),(15), (17),(20)

The following query will return

14<br />

SQL

SELECT  TOP 1 ChNo + 1
FROM    temp mo
WHERE   NOT EXISTS
        (
            SELECT  NULL
            FROM    temp mi
            WHERE   mi.ChNo = mo.ChNo + 1
        )
ORDER BY
        ChNo

Adapted from the accepted answer to this post[^]
Other solutions are discussed here[^]

Posted 12-Oct-15 5:18am

CHill60

Comments

Maciej Los 12-Oct-15 11:48am

5ed!

Sathish km 14-Oct-15 0:15am

Sorry for no reply, Thks!
Why you are inserting to temp (11),(12),(13),(15), (17),(20)

i want to insert value into Row Column from front end,by that i/p we have to check it in sql which is available, if not, skip & increment to check next number.

CHill60 16-Oct-15 10:09am

I was recreating the test data that you presented so I could test my solution

Sathish km 17-Oct-15 0:13am

ok