Auto-Incrementing Values for Insert Query

Question

User-972094631 posted

Hello,

Using VS2013, .NET framework 4.5, C# and DB2 database.

This is inserting into a DB2 table with a PK field that cannot be defined as a auto-incrementing, identity_seed column with a unique constraint (don't ask me why). So, I will need to select the MAX transaction_id and would like to be able to insert my own sequential, auto-incrementing number. If someone could help me with this I would greatly appreciate it. I've done some research on this and have come up with some variations of something like this: for (int i = 0; i < result.Count(); i++), but am not sure how to implement this in my existing code:

try
            {
                query = new StringBuilder();
		
                query.AppendLine("INSERT INTO " + "TEST_TABLE");
                query.AppendLine(" (TRANSACTION_ID, TEST_CODE, TEST_CODE_1, TEST_CODE_2, USER_ID, TIME_STAMP) ");
                query.AppendLine("VALUES (");
                query.AppendLine("" + items.TRANSACTION_ID + "");     //TO DO: self incrementing loop
                query.AppendLine("," + items.TEST_CODE + "");
                query.AppendLine(", " + items.TEST_CODE_1 + "");
                query.AppendLine(", '" + items.TEST_CODE_2 + "'");           
                query.AppendLine(", '" + items.USER_ID + "'");
                query.AppendLine(", '" + items.TIME_STAMP + "'"); 
		
                query.Append(");");

                base.ExecuteNonQuery(_DBCommand, query.ToString());
            }                
            catch (Exception ex)
            {
                throw ex;
            }
        }

Any help with this or a better solution would be greatly appreciated.

Thanks,

Buster

Answer 1

User-1360095595 posted

For that "identity" column, use SELECT MAX(column) +1 FROM table and that will be the value you insert. 

Answer 2

User-972094631 posted

THANK YOU!!!

Answer 3

User-972094631 posted

May I please ask one more question? Can I do something similar (I'm doing this which works for the MAX id):

query.AppendLine("(SELECT MAX(transaction_id) + 1 FROM TEST_TABLE)");  

for selecting the most recently inserted record for a particular value? For example, I have two records that are dups except for a flag indicator:

id | value_1 | value_2 | value_3 | flag

1  |    123    |    abc    |  T1Ad5  |   Y

2  |    123    |    abc    |  T1Ad5  |   N

3  |    456    |    def    |  T1AdZ  |   Y

4  |    456    |    def    |  T1AdZ  |   N

So for 123, abc, T1Ad5 I need to return the record with id 2 and flag = N

and for 456, def, T1AdZ I need to return the record with id 4 and flag = N

Hope this makes sense.

Thanks,

Buster

Answer 4

User-1360095595 posted

You can add a where clause, I think. Like: WHERE flag = 'N'

Edit: As long as the 'N' record id will always be greater than the 'Y' record.

Answer 5

User-972094631 posted

No, unfortunately, this will not be the case because they can select Y or N as many times as they want causing an insert each time. Any other ideas?

Answer 6

User-1360095595 posted

One more idea. Add a datetime column to your table. Set it to default to GETDATE(). You can use this field to determine the newer record(s).

Answer 7

User-972094631 posted

Brilliant, thank you again! Much appreciated. --Buster