How to Insert Records based on the Previous Insert?

Question

Here's my procedure:

PROCEDURE add_values
AS
BEGIN

   INSERT INTO TABLE_A ...
   SELECT t.id, t.name FROM TABLE_C ("This selection will return multiple records")

END

While it inserts in TableA, I would like insert into another table(TableB) for that particular record which got inserted in tableA.

The columns in TableA and TableB are different. Is it wise to call a function before inserting into TableB - I would like to perform certain gets and sets based on the id inserted in tableA?

Answer 1

In mysql try INSERT..SELECT

INSERT INTO tableB SELECT * FROM tableA where id = LAST_INSERT_ID();

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

Answer 2

you can create a trigger to do it, if this is the only way you insert data into the table. (otherwise it will run on every insert)

or you can iterate on a cursor which will enable you to do it with every insert from the selection

(Cursor example

Answer 3

Try this

Inputs

declare @tblA table (id int,name varchar(20))
declare @tblB table (id int,name varchar(20))
declare @tblC table (id int identity,name varchar(20))
insert into @tblC 
    select 'name1' union all select 'name2' union all
    select 'name3' union all select 'name4' union all
    select 'name5' union all select 'name6' union all
    select 'name7' union all select 'name8' union all
    select 'name9' union all select 'name10' union all
    select 'name11' union all select 'name12' union all
    select 'name13' union all select 'name14' union all
    select 'name15' union all select 'name16' union all
    select 'name17' union all select 'name18' union all
    select 'name19' union all select 'name20'

Query

insert @tblA 
output INSERTED.id, INSERTED.Name
into @tblB 
select 
    id,name
from @tblC 
where id % 2 = 0

select * from @tblA
select * from @tblB

Output: [ For both table A & B]

id name

2   name2
4   name4
6   name6
8   name8
10  name10
12  name12
14  name14
16  name16
18  name18
20  name20

Basically I am inserting those records into TableA from TableC whose id's are even. And then by using Output clause inserting the values from TableA to TableB

For more information OUTPUT Clause

Hope this makes sense

Answer 4

The best technique depends on the details of your table definitions and the associated queries. Based on the info in your question, maybe something like this:

CREATE PROCEDURE add_values
AS
BEGIN
  BEGIN TRANS
    INSERT INTO TableA
      SELECT id, name FROM TableC
    INSERT INTO TableB
      SELECT id, name FROM TableC
  COMMIT
END