Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jul 2006
    Posts
    124
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Can I use a query to find the next key in SQL Server?

    Say the table has a primary key, and the latest value on it is 100, then all the records on that table are deleted... If I INSERT new record, the key will be 101... Can I find out what the key is going to be before inserting a new record to the table?

    edit:
    Found an answer to my own question:
    SELECT IDENT_CURRENT ('table')

    This will return the current, but you can just add "+ 1" without the quotes, to get the next one.

    Last edited by davidc2; 05-06-2008 at 03:49 PM.

  • #2
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    380
    Thanks
    9
    Thanked 39 Times in 39 Posts
    You can make the value auto-increment in the database, and don't insert that column in your insert query.

    Or

    Code:
    SELECT MAX(id) FROM TABLE
    Regards, Stooshie
    O

  • #3
    Regular Coder
    Join Date
    Jul 2006
    Posts
    124
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Stooshie View Post
    You can make the value auto-increment in the database, and don't insert that column in your insert query.
    I actually need both.. It's a weird scenario I guess heh

    Quote Originally Posted by Stooshie View Post
    Or

    Code:
    SELECT MAX(id) FROM TABLE
    That won't work if the database is empty, because it returns NULL.

    And it will also won't work if I delete row with id x, and there's a x-1.. A new row would have id x+1, not x.
    For instance:
    Table:
    row with id 79
    row with id 80 -- delete this

    Result:
    row with id 79

    Insert new values into table result:
    row with id 79
    row with id 81

    Thanks for your reply though, it will sure clear questions for future comers

  • #4
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    380
    Thanks
    9
    Thanked 39 Times in 39 Posts
    Not sure what you mean about needing both.

    If you have auto-increment set in the DB you shouldn't have to (in fact, shouldn't) insert the id column in your query at all.

    You are right that using SELECT MAX(id) only works if you do not need to rely on the absolute uniqueness of an ID. However, if that is the case, again, you should use auto-increment.
    Regards, Stooshie
    O

  • #5
    Regular Coder
    Join Date
    Jul 2006
    Posts
    124
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Stooshie View Post
    Not sure what you mean about needing both.

    If you have auto-increment set in the DB you shouldn't have to (in fact, shouldn't) insert the id column in your query at all.

    You are right that using SELECT MAX(id) only works if you do not need to rely on the absolute uniqueness of an ID. However, if that is the case, again, you should use auto-increment.
    By both I mean that I need a string field to be "'text'+table_id", and also table_id.

    You are right about the auto-increment, but when I'm inserting I want a string field (which is not the identity column (*)), to have that id value too.

    (*) My identity column is an auto-increment.

  • #6
    Regular Coder ralph l mayo's Avatar
    Join Date
    Nov 2005
    Posts
    951
    Thanks
    1
    Thanked 31 Times in 29 Posts
    Quote Originally Posted by davidc2 View Post
    I want a string field (which is not the identity column (*)), to have that id value too.
    Sounds like a job for a view if you're using mysql 5 (edit: doh, you're obvious not, but anyway...)

    Code:
    mysql> create table v(id int not null primary key auto_increment, text_field text);
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> insert into v(text_field) values ('asdf');
    Query OK, 1 row affected (0.05 sec)
    
    mysql> insert into v(text_field) values ('qwerty');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> create view v_view as select *, concat(text_field, ' ', cast(id as char)) AS whatever from v;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from v_view;
    +----+------------+----------+
    | id | text_field | whatever |
    +----+------------+----------+
    |  1 | asdf       | asdf 1   |
    |  2 | qwerty     | qwerty 2 |
    +----+------------+----------+
    2 rows in set (0.00 sec)
    edit: as, sorry, I came to this from the front page and didn't even notice it was about MS SQL, but I imagine the procedure is quite similar.
    Last edited by ralph l mayo; 05-06-2008 at 10:58 PM.

  • #7
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    380
    Thanks
    9
    Thanked 39 Times in 39 Posts
    You could do the concatenation in the SELECT statement and not have the third column.
    Code:
    SELECT CONCAT(CAST(id AS CHAR),'-',field2) AS field3
    FROM table
    sorry, in MSSQL appears to be something like
    Code:
    SELECT {FN CONCAT(forename, ' ', surname)} as fullname
    Regards, Stooshie
    O

  • #8
    Regular Coder
    Join Date
    Jul 2006
    Posts
    124
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I had to use this on a different database recently, this time Adaptive Server Anywhere (ASA), not sure if it works in MSSQL, but this I think is TransactSQL so it might, or at least have a similar function.

    SELECT @@IDENTITY AS ID


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •