I am trying to insert a record in a table (SQL-Server) using the INSERT INTO <talbe_name>... command. The values are passed ok and the record is insert in the table without any problem. This particular table has an "id" column which is set by the sql-server (identity field). I want to take that particular id for later use. How can I get that value? Is there a way to get it without using a stored procedure?
Please let me know if you have any ideas.
Jimmy.What you can do is get a total record count, and increment it by 1 (before you add the record) to figure out what its going to be. Otherwise do a total record count after the record is added, and you'll get your id # (as it will be the last one added)
Regards,I tried to get a record count after the insertion but it doesn't seem to be working. However, the record is inserted in the table correctly. What could be the problem?
Jimmy.Jimmy,
The method Jacob mentioned will work, but you'll run into a problem when you delete a record from your table. If you had 60 records in the table, and you deleted 5, SQL will use 61 as the next id value when you insert a new record, but your recordcount will be 56.
I run in to this situation frequently at work and use the MAX function to handle it. After you run your INSERT query, immediately run a query like
SELECT MAX (IdentityColumn) AS Newest FROM TableName
Newest will be the id of the record you just inserted into the table.A way I have done it in the past is to read the DB in descending order after you have added the new record and read the first record id as this will be your most recent addition. You have to make sure no other update, deletes etc are made when you do this by locking the db.
Please let me know if you have any ideas.
Jimmy.What you can do is get a total record count, and increment it by 1 (before you add the record) to figure out what its going to be. Otherwise do a total record count after the record is added, and you'll get your id # (as it will be the last one added)
Regards,I tried to get a record count after the insertion but it doesn't seem to be working. However, the record is inserted in the table correctly. What could be the problem?
Jimmy.Jimmy,
The method Jacob mentioned will work, but you'll run into a problem when you delete a record from your table. If you had 60 records in the table, and you deleted 5, SQL will use 61 as the next id value when you insert a new record, but your recordcount will be 56.
I run in to this situation frequently at work and use the MAX function to handle it. After you run your INSERT query, immediately run a query like
SELECT MAX (IdentityColumn) AS Newest FROM TableName
Newest will be the id of the record you just inserted into the table.A way I have done it in the past is to read the DB in descending order after you have added the new record and read the first record id as this will be your most recent addition. You have to make sure no other update, deletes etc are made when you do this by locking the db.