Sql - Inserting a Row and Returning Primary Key

SQL - Inserting a row and returning primary key

For MS SQL Server:

SCOPE_IDENTITY() will return you the last generated identity value within your current scope:

SELECT SCOPE_IDENTITY() AS NewID

Getting the primary key of an newly inserted row in SQL Server 2008

If you're inserting a whole set of rows, selecting the SCOPE_IDENTITY() won't do. And SCOPE_IDENTITY also only works for (numeric) identity columns - sometimes your PK is something else...

But SQL Server does have the OUTPUT clause - and it's very well documented on MSDN!

INSERT INTO dbo.Table(columns)
OUTPUT INSERTED.p_key, INSERTED.someothercolumnhere .......
VALUES(...) 

Those values will be "echoed" back to the calling app, e.g. you'll see them in a grid in SQL Server Management Studio, or you can read them as a result set from your C# or VB.NET calling this INSERT statement.

How do I insert into a table and get back the primary key value?

insert into YourTable values (...)

get the new PK with scope_identity()

select scope_identity()

Get the new record primary key ID from MySQL insert query?

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Eg:

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

Return primary key of inserted row using SQLAlchemy & SQL Server

AlwaysLearning's solution is correct. Append "SET NOCOUNT ON;" to the top of the query, e.g.

sql = """
SET NOCOUNT ON;
INSERT INTO [workflow_execution_status]([workflow], [requested_by], [requested_datetime], [experiment_id])
OUTPUT Inserted.execution_id 
VALUES (?, ?, ?, ?)"""

INSERT INTO get primary key ID from current

If you're working in a reasonably recent version of Microsoft SQL Server, you can use OUTPUT INSERTED.columnname with your identity column, like this:

<cfquery name="newAction" datasource="RC">
INSERT INTO myTable 
(
  name,
  address,
  title
) 
OUTPUT INSERTED.ID
VALUES 
(
  #fname#,
  #address#,
  #usrtitle#
)
</cfquery>

<cfset actionUpdateID = newAction.person_id>

The example assumes that ID is your identity column.

(It also shouldn't need to be said that you should be using <cfqueryparam ...>.)

---

Alternatively, assuming you're using a reasonably recent version of ColdFusion, the metadata returned by CFQUERY includes the identity on insert, so long as you define a name for the result="" attribute. If you used myResult for the name of the result variable, you can use (depending on your flavor of database):

myResult.IDENTITYCOL    SQL Server only. The ID of an inserted row.

myResult.ROWID          Oracle only. The ID of an inserted row. This is not
                        the primary key of the row, although you can retrieve
                        rows based on this ID.

myResult.SYB_IDENTITY   Sybase only. The ID of an inserted row.

myResult.SERIAL_COL     Informix only. The ID of an inserted row.

myResult.GENERATED_KEY  MySQL only. The ID of an inserted row. MySQL 3 
                        does not support this feature.

myResult.GENERATEDKEY   Supports all databases. The ID of an inserted row.

(ColdFusion documentation)

So the example above would look like this:

<cfquery name="newAction" datasource="RC" result="queryResults">
INSERT INTO myTable 
(
  name,
  address,
  title
) 
VALUES 
(
  #fname#,
  #address#,
  #usrtitle#
)
</cfquery>

<cfset actionUpdateID = queryResults.IDENTITYCOL>

Is it possible to return the Primary Key on an Insert as select statement - Oracle?

I don't believe you can do this with insert/select directly. However, you can do it with PL/SQL and FORALL. Given the constraint about the table size, you'll have to balance memory usage with performance using l_limit. Here's an example...

Given this table with 100 rows:

create table t (
  c  number generated by default as identity,
  c2 number
);

insert into t (c2)
select rownum
from dual
connect by rownum <= 100;

You can do this:

declare

  cursor t_cur
  is
    select c2
    from t;

  type t_ntt is table of number;

  l_c2_vals_in t_ntt;
  l_c_vals_out t_ntt;
  l_limit      number := 10;

begin

  open t_cur;

  loop
    fetch t_cur bulk collect into l_c2_vals_in limit l_limit;

    forall i in indices of l_c2_vals_in
    insert into t (c2) values (l_c2_vals_in(i))
    returning c bulk collect into l_c_vals_out;

    -- You have access to the new ids here
    dbms_output.put_line(l_c_vals_out.count);

    exit when l_c2_vals_in.count < l_limit;
  end loop;

  close t_cur;

end;

Insert new row and get primary key or get primary key of existing record

Credit goes to Clodoaldo Neto for his answer.

I'm putting my answer here because while his answer did point me in the direction it is slightly different, and I've simplified mine a bit.

WITH 
    existing AS (SELECT id FROM tag WHERE text='hey'),
    new AS (INSERT INTO tag (text) SELECT 'hey' WHERE NOT EXISTS (SELECT 1 FROM existing) RETURNING id)

SELECT id FROM existing UNION ALL SELECT id FROM new

Insert a single row and return its primary key

Good question, took me a few tries to figure it out. Declare an SSIS variable of type Int32 (unless you need sizing for a bigint or numeric). I chose tablePk as mine.

Option 1

Execute SQL Task

• General tab

ResultSet: None

SQL

INSERT INTO dbo.ImportData (EndDate) VALUES (NULL);
SELECT ? = SCOPE_IDENTITY()

• Parameter Mapping tab

Variable Name: User::tablePk

Direction: Output

Data Type: Long

Parameter Name: 0

Parameter Size: -1

Option 2

This was the original solution as I couldn't grok how to get the placeholder ? in a normal query. It couldn't as simple as what I had above, except it was.

The only difference is the query used

SQL

DECLARE @sql nvarchar(500)
, @paramDef nvarchar(500)

SELECT
    @sql = N'INSERT INTO dbo.ImportData (EndDate) VALUES (NULL);
    SELECT @ident = SCOPE_IDENTITY();'
,   @paramDef = N'@ident int OUTPUT'
EXECUTE sp_executesql @sql, @paramDef, @ident = ? OUTPUT

Option 3

If you're using a data flow, I outline an approach on How to Add the Result Set from a T-SQL Statement to a Data Flow? In short, you need to add a column into the data flow prior to an OLE DB Command. Within the OLE DB Command, you will map that empty column into a OUTPUT parameter from your stored procedure and then as the stored procedure fires, it will replace the column with the value from the procedure.

---

Related Topics