Select The Rows That Just Inserted

Select the rows that just inserted

I'm not 100% sure what exactly you want back.... but SQL Server has an OUTPUT clause that can output stuff from INSERT and UPDATE and DELETE statements:

INSERT INTO dbo.YourTable(col1, col2, ..., colN)
OUTPUT Inserted.Col1, Inserted.IDCol, Inserted.Col17
VALUES(val1, val2, ...., valN)

Here, you're inserting values and the inserted values for the IDCol (e.g. an INT IDENTITY column), Col1 and Col17.

If just getting back the results into your grid in Mgmt Studio is good enough - then use the OUTPUT clause! Read more about the OUTPUT clause on Books Online

Return inserted row

You could insert the row and then use SCOPE_IDENTITY() to get the ID of the row you inserted and return the row with that id from the table.

For example:

INSERT INTO tableA VALUES (a1, a2);

DECLARE @Id INT = SCOPE_IDENTITY();

SELECT * FROM tableA WHERE ID = @Id

TSQL - select inserted rows

You can easily use the OUTPUT clause to output e.g. the newly created ID's as a result set, and you can read those from your C# app use a standard SqlDataReader:

INSERT INTO tbl (id)
OUTPUT Inserted.Id
   SELECT COUNT(*) + 1 AS id from tbl

Update: the data returned by the OUTPUT clause can be captured from the C# side as if it were a regular SELECT statement:

string insertStmt = "INSERT INTO tbl (id) " + 
                    " OUTPUT Inserted.Id " + 
                    " SELECT COUNT(*) + 1 AS id from tbl";

using (SqlConnection conn = new SqlConnection(-your-connection-string-here-))
using (SqlCommand cmd = new SqlCommand(insertStmt, conn))
{
    conn.Open();

    // execute your INSERT statement into a SqlDataReader
    using(SqlDataReader reader = cmd.ExecuteReader())
    {
        // read the values returned from the OUTPUT clause
        while(reader.Read())
        {
            int insertedID = reader.GetInt32(0);
            // do something with those values....                
        }
    }

    conn.Close();
}

How can I retrieve the identities of rows that were inserted through insert...select?

You can get this information using the OUTPUT clause.

You can output your information to a temp target table or view.

Here's an example:

DECLARE @InsertedIDs TABLE (ID bigint)
INSERT into DestTable (col1, col2, col3, col4)
OUTPUT INSERTED.ID INTO @InsertedIDs
SELECT col1, col2, col3, col4 FROM SourceTable

You can then query the table InsertedIDs for your inserted IDs.

How to get the identity of an inserted row?

• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

• SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.

• IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."

• The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

How to select all inserted rows to execute an insert trigger with a stored procedure in postgresql?

You create a statement lever trigger, but do not attempt to pass parameters. Instead use the clause referencing new table as reference_table_name. In the trigger function you use the reference_table_name in place of the actual table name. Something like: (see demo)

create or replace function group_a_ais()
  returns trigger
 language 'plpgsql'
as $$
begin
    insert into  table_b(track_code, items)
          select track_code, count(*)
            from rows_inserted_to_a
           group by track_code ;
    return null;
end;
$$;

create trigger table_a_ais
    after insert on table_a
    referencing new table as rows_inserted_to_a
    for each statement  
        execute function group_a_ais(); 

Do not attempt to commit in a trigger, it is a very bad id even if allowed. Suppose the insert to the main table is part of a larger transaction, which fails later in its process.

Be sure to refer to links provided by Adrian.

How to get the just inserted row in SQL Server stored procedure (without using trigger)?

You can use the OUTPUT clause. You can even combine both inserts into one composite:

create table T1 (ID int IDENTITY(1,1) not null,ColA varchar(10) not null)
create table T2 (ID int IDENTITY(1,1) not null,T1ID int not null,ColB varchar(10) not null)

--Look ma! no local variables at all
insert into T2 (T1ID,ColB)
select t1.ID,'def'
from (
    insert into T1(ColA)
    output inserted.ID
    values ('abc')
    ) t1

select * from T1
select * from T2

Results:

ID          ColA
----------- ----------
1           abc

ID          T1ID        ColB
----------- ----------- ----------
1           1           def

Mysql fetch the row just inserted

its not very logical but you could:

insert into `table1` (`column1`,`column2`,`column3`) VALUES ("value1","value2","value3");
select * from `table1` where `PK`=LAST_INSERT_ID();

instead you should only SELECT LAST_INSERT_ID(); as jurgen d suggested and reuse the other data

---

Related Topics