locked
Auto-Incrementing Values for Insert Query RRS feed

  • 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

    Tuesday, July 1, 2014 6:54 PM

Answers

  • User-1360095595 posted

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 1, 2014 6:57 PM
  • 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).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 2, 2014 2:00 PM

All replies

  • User-1360095595 posted

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 1, 2014 6:57 PM
  • User-972094631 posted

    THANK YOU!!!

    Tuesday, July 1, 2014 10:33 PM
  • 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

    Tuesday, July 1, 2014 11:09 PM
  • 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.

    Wednesday, July 2, 2014 4:51 AM
  • 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?

    Wednesday, July 2, 2014 1:50 PM
  • 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).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 2, 2014 2:00 PM
  • User-972094631 posted

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

    Wednesday, July 2, 2014 6:16 PM