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

[davidc2]

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.

:D

[Stooshie]

You can make the value auto-increment in the database, and don't insert that column in your insert query.

Or


SELECT MAX(id) FROM TABLE

[davidc2]

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


Or


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

[Stooshie]

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.

[davidc2]

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.

[ralph l mayo]

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...)


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.

[Stooshie]

You could do the concatenation in the SELECT statement and not have the third column.

SELECT CONCAT(CAST(id AS CHAR),'-',field2) AS field3
FROM table

sorry, in MSSQL appears to be something like

SELECT {FN CONCAT(forename, ' ', surname)} as fullname