Insert Inserted Id to Another Table

How to insert a row into another table using last inserted ID?

You don't need a trigger or any of the @@IDENTITY, SCOPE_IDENTITY() functions. All of them have restrictions and none of them can deal with values that aren't produced by an IDENTITY constraint. None of them can deal with multiple insertions either.

You can use the OUTPUT clause of INSERT to copy newly inserted values into another table or a table variable.

OUTPUT is also available for UPDATE and DELETE. You can retrieve the new/modified columns with the inserted. and deleted. prefixes

For these tables :

create table #GamerName 
(
    ID int IDENTITY primary key, 
    Name nvarchar(20) not null
);
create table #GamerValues(
    ID int IDENTITY primary key,
    GamerID int not null,
    Score int not null
);

You can insert new records in #GamerName and copy the generated ID to #GamerValues with :

INSERT INTO #GamerName (Name)
OUTPUT inserted.ID,0 into #GamerValues(GamerID,Score)
VALUES 
('Jeff'),
('Geoff'),
('Jarrod'),

New values appear in the inserted virtual table. OUTPUT is also availa

A new line will be created for each of the gamers in GamerValues. Let's modify the default score with :

UPDATE #GamerValues 
SET Score=100

The table will look like :

ID  GamerID Score
1   1       100
2   2       100
3   3       100

Adding another gamer with

insert into #GamerName (Name)
output inserted.ID,0 into #GamerValues(GamerID,Score)
Values 
('Joel sp')

Will result in a new line with a Score of 0

ID  GamerID Score
1   1       100
2   2       100
3   3       100
4   4       0

Insert inserted id to another table

Avoid rules, as they'll come back to bite you.

Use an after trigger on table a that runs for each row. It should look something like this (untested):

create function a_ins() returns trigger as $$
begin
  insert into b (a_id) values (new.id);
  return null;
end;
$$ language plpgsql;

create trigger a_ins after insert on a
for each row execute procedure a_ins();

Insert into a table after getting an ID from another table

An INSERT statement can use the rows returned by a SELECT statement as source for inserting data. So construct the appropriate SELECT statement from posts_temp and authors and then you are done:

INSERT INTO posts(text, author_id)
  SELECT pt.post, a.id
  FROM posts_temp pt
  JOIN authors a ON a.name = pt.author;

Insert OUTPUT Insert.id to another table in multiple values insert

You can insert the output directly into the user_log table:

BEGIN TRANSACTION

INSERT INTO [User] (ID, Email)
OUTPUT inserted.id, CURRENT_TIMESTAMP INTO user_log(id, date)
VALUES (1, 'a@x.com'), (2, 'b@x.com');

COMMIT TRANSACTION  

Example on SQL Fiddle

---

If you need to return the ids you can just add a second OUTPUT clause:

BEGIN TRANSACTION

INSERT INTO [User] (ID, Email)
OUTPUT inserted.id, CURRENT_TIMESTAMP INTO user_log(id, date)
OUTPUT inserted.id                      
VALUES (1, 'a@x.com'), (2, 'b@x.com');

COMMIT TRANSACTION  

How to get the last inserted id to insert it to another table?

Option 1 (for Integer based IDs)

Dim LASTID
Dim selectquery As String = "SELECT MAX(WHATYOUNAMEYOURID) FROM IndustrialEstablishmentFood_tbl GROUP BY WHATYOUNAMEYOURID" 

Dim cmd As New SqlCommand(selectquery , connection)
cmd.CommandType = CommandType.Text
LASTID = cmd.ExecuteScalar

MessageBox.Show("ID Found!", LASTID, MessageBoxButtons.OK, MessageBoxIcon.Information)

Option 2 (Optimal & works with GUID) - Create a return in your query to return @@IDENTITY

MYSQL Insert id from another table

Try this

insert into tab2 (id_customers, value)
values ((select id from tab1 where customers='john'), 'alfa');

Miss out brackets

Hope it helps

---

Related Topics