SQL: How to get the id of values I just INSERTed?
I inserted some values into a table. There is a column whose value is auto-generated. In the next statement of my code, I want to retrieve this value.
Can you tell me how to do it the right way?
21 Answers 21
@@IDENTITY is not scope safe and will get you back the id from another table if you have an insert trigger on the original table, always use SCOPE_IDENTITY()
This is how I do my store procedures for MSSQL with an autogenerated ID.
This works very nicely in SQL 2005:
It has the benefit of returning all the IDs if your INSERT statement inserts multiple rows.
If your using PHP and MySQL you can use the mysql_insert_id() function which will tell you the ID of item you Just instered.
But without your Language and DBMS I’m just shooting in the dark here.
Again no language agnostic response, but in Java it goes like this:
If you are working with Oracle:
Inset into Table (Fields. ) values (Values. ) RETURNING (List of Fields. ) INTO (variables. )
INSERT INTO PERSON (NAME) VALUES (‘JACK’) RETURNING ID_PERSON INTO vIdPerson
or if you are calling from. Java with a CallableStatement (sry, it’s my field)
INSERT INTO PERSON (NAME) VALUES (‘JACK’) RETURNING ID_PERSON INTO ?
and declaring an autput parameter for the statement
There’s no standard way to do it (just as there is no standard way to create auto-incrementing IDs). Here are two ways to do it in PostgreSQL. Assume this is your table:
You can do it in two statements as long as they’re consecutive statements in the same connection (this will be safe in PHP with connection pooling because PHP doesn’t give the connection back to the pool until your script is done):
lastval() gives you the last auto-generated sequence value used in the current connection.
The other way is to use PostgreSQL’s RETURNING clause on the INSERT statement:
This form returns a result set just like a SELECT statement, and is also handy for returning any kind of calculated default value.
An important note is that using vendor SQL queries to retrieve the last inserted ID are safe to use without fearing about concurrent connections.
I always thought that you had to create a transaction in order to INSERT a line and then SELECT the last inserted ID in order to avoid retrieving an ID inserted by another client.
But these vendor specific queries always retrieve the last inserted ID for the current connection to the database. It means that the last inserted ID cannot be affected by other client insertions as long as they use their own database connection.
Assuming the following table definition:
You can use the following:
Which will return the value of the ID column.
From the site i found out the following things:
SELECT @@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY() It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’) It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
Get an Object’s ID from its Name in SQL Server: OBJECT_ID()
In SQL Server, you can use the OBJECT_ID() function to return an object’s ID, based on its name.
This can be useful when you need an object’s ID, but you only know its name.
The official definition of OBJECT_ID() is that it returns the database object identification number of a schema-scoped object .
Example 1 – Basic Usage
Here’s a basic example to demonstrate how it works.
In this case, the current database does contain an object called Albums , and its ID is 885578193. This is the ID that you can find in the object_id column of the sys.objects system catalog view.
Example 2 – Check the sys.objects View
Here’s another basic example to verify what I just said.
The sys.objects system catalog view contains a row for each user-defined, schema-scoped object that is created within a database.
In this example, the first two columns display the object’s name and object_id respectively.
In the third column of this example, I use OBJECT_ID() to return the object’s ID based on its name. To do this, I pass the name column to the OBJECT_ID() function.
This is obviously just an example, and using OBJECT_ID() was unnecessary in this case, because sys.objects already returns the object’s ID.
Example 3 – A More Useful Example
In this example, I use OBJECT_ID() in a WHERE clause so that I only get results related to the table called Client .
In this case I wanted to see which entities depend on the Client table (i.e. which entities reference that table in their SQL code). The referenced_id column uses the object’s ID, so by using OBJECT_ID() , I was able to get the ID of the Client table and compare it to referenced_id .
See Find Dependencies in SQL Server: sql_expression_dependencies for a more detailed explanation of this query, and related examples.
Example 4 – Fully Qualified Names
You also have the option of qualifying the object name with the schema name, and also the database name if so desired.
Here’s a simple example to demonstrate:
Here it is again, this time using square brackets as delimiters:
If you ever get a NULL result even though you know the object exists, try qualifying it with the schema (and even the database name).
Example 5 – Cross Database Queries
By default, SQL Server assumes that the object name is in the context of the current database. You can use a 3 part name to specify an object in a different database.
Here’s the same code from the previous example, except this time I run the code twice: the first time it’s run in the Music database, the second time it’s run in the WideWorldImportersDW database:
In the first result, all three columns return the correct ID. This is because the object happens to be in the Music database.
In the second result, only the 3 part name is able to find the correct object. This is to be expected, because the 1 part and 2 part names don’t specify the name of the database, therefore it assumes the object is in the WideWorldImportersDW (wrong) database.
Example 6 – Specify the Object Type
The OBJECT_ID() function also accepts an argument for the object type. This argument, if provided, comes after the object’s name.
Here, I specify that the object type is U , which means “Table (user-defined)”.
Oracle DBA Tweets . Learning is Fun-tastic
Oracle DBA Tweets . Learning is Fun-tastic . You must stop talking about the problem and start talking about the solution, start speaking words of victory. We should never stop working on ourselves. Always be positive and keep smiling..have a great day friends 🙂 Source: Internet
July 24, 2013
How to find SQL,SQL_ID history on Oracle
Session related Queries
Last/Latest Running SQL
————————
set pages 50000 lines 32767
col «Last SQL» for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text «Last SQL»
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value
/
Current Running SQLs
———————
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,’DD-MON-RR HH24:MI’) login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
— and S.status=’ACTIVE’
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like ‘select S.USERNAME,S.seconds_in_wait%’
/
Current Running SQLs
———————
set pages 50000 lines 32767
col program format a20
col sql_text format a50
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/
Last/Latest Running SQL
————————
set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc
/
SQLs Running from longtime
—————————
alter session set nls_date_format = ‘dd/mm/yyyy hh24:mi’;
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
,opname
,target
,round(sofar/totalwork*100,2) as percent_done
,start_time
,last_update_time
,time_remaining
from
v$session_longops
/
Active Sessions running for more than 1 hour
———————————————
set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40
SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,’dd-mm-yy hh:mi:ss AM’)»Logon Time»,
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS=’ACTIVE’
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;
Session details associated with SID and Event waiting for
———————————————————
set pages 50000 lines 32767
col EVENT for a40
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, ‘dd-mon-yy hh24:mi’) LOGON_TIME,to_char(Sysdate, ‘dd-mon-yy-hh24:mi’) CURRENT_TIME, (a.last_call_et/3600) «Hrs connected» from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;
Session details associated with Oracle SID
——————————————-
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
‘Session Id. ‘||s.sid,
‘Serial Num. ‘||s.serial#,
‘User Name . ‘||s.username,
‘Session Status . ‘||s.status,
‘Client Process Id on Client Machine . ‘||’*’||s.process||’*’ Client,
‘Server Process ID . ‘||p.spid Server,
‘Sql_Address . ‘||s.sql_address,
‘Sql_hash_value . ‘||s.sql_hash_value,
‘Schema Name . . ‘||s.SCHEMANAME,
‘Program . ‘||s.program,
‘Module . ‘|| s.module,
‘Action . ‘||s.action,
‘Terminal . ‘||s.terminal,
‘Client Machine . ‘||s.machine,
‘LAST_CALL_ET . ‘||s.last_call_et,
‘S.LAST_CALL_ET/3600 . ‘||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl(‘&sid’,s.sid)
/
set head on
Checking for Active Transactions SID
————————————
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;
Session details from Session longops
————————————-
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;
Session details with SPID
————————-
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,’yyyy-mm-dd hh24:mi:ss’)
from v$session where paddr in (select addr from v$process where spid = ‘&spid’)
/
To find Undo Generated For a given session
——————————————
select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid=’&sid’;
To list count of connections from other machines
————————————————
select count(1),machine from gv$session where inst_id=’&inst_id’ group by machine;
To get total count of sessions and processes
———————————————
select count(*) from v$session;
select count(*) from v$process;
select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;
To find sqltext thru sqladdress
——————————-
select sql_address from v$session where sid=1999;
select sql_text from v$sqltext where ADDRESS=’C00000027FF00AF0′ order by PIECE;
To find sqltext for different sql hashvalue
——————————————-
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)
To list long running forms user sessions
—————————————-
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ and
p.addr=s.paddr ;
To list inactive Sessions respective username
———————————————
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS=’INACTIVE’
group by username
order by num_inv_sess DESC;
SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS=’INACTIVE’;
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS=’INACTIVE’;
To find session id with set of SPIDs
————————————
select sid from v$session, v$process where addr=paddr and spid in (‘11555′,’26265′,’11533’);
To find Sql Text given SQLHASH & SQLADDR
—————————————-
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS =’&addr’ order by piece;
select piece,sql_text from v$sqltext where ADDRESS =’&addr’ order by piece;
Как узнать id sql
I know this might seem a simple question — for which you might think existing answers exist. However .
Understand that I want it be reasonable in performance, so it allows to be logged for every single query executed — or at least the big ones — without much overhead.
My first idea was this query:
My idea was if I run this right after my target query, I will capture the correct sql_id through prev_sql_id.
However . I was not . I was getting a different SQL . apparently in between my target SELECT statement and the query for prev_sql_id , something else ran. In my case Auditing is enabled, and I was capturing the insert into the SYS.AUD$ table. No good.
As my main purpose for this attempt was to capture the execution plan for the query (as it was executed and captured by the shared pool), I thought that instead I can simply run this query:
Documentation states that with NULL SQL_ID as parameter, it will run the explain plan on the most recent query ran. I was hoping that this would take care of earlier issues. However . I got the plan for the exact same insert into the SYS.AUD$ table.
You might say, ok, then just simply put a comment in your query that allows to easily capture the SQL_ID , like in following:
Then I can try to find the SQL_ID as follows:
That will give me several possible candidates, of which the V$SQLAREA query itself is also included. The problem here is that I will need to randomize every query ran, which would cause me to always have a hard-parse.
I have tried other solutions where I go through history, but that comes at a much bigger cost. I have tried to search other solutions. They all seem to lag in some way.