I want to insert a record in some tables using a simple stored procedure.My question is: do i have to write several inserting procedures (one for each table) or is it possible that i can pass as a variable the name of the table in which i want to insert the reccord?i've tried to do the latter but it didn't work...is there any way? or should i write one procedure for each table?

Recommended Answers

All 6 Replies

Considering each table has a different range of columns and data types a stored procedure for each is probably required. If you could use the same insert procedure to insert the same data into several tables that begs the question of why you have these others tables in the first place.

Hello,

No you don;t have to have a separate procedure for each and (sorry hericles) I can think of a hundred reasons to insert into multiple tables from a single record. a simple example would be adding an item to inventory that is a part of another item and you have to create the accounting records at the same time.

But I digress, basically all you have to do is treat each insert as a separate query and then put them together after each one works. Below is a query I used to link an unlimited number or items to other items. It will probably not make a lot of sense since you don't have the original tables but I was all part of a procedure called by the insert trigger. SID (server ID), CID (Customer ID), SLAB (Server Label), RID (Rack ID) were some of the parameters passed into the procedure. SOID was the server Object ID. Trust me you don;t want to know what we did but the example is what you are trying to do.

begin

declare SOID INT;

declare ROID INT;

declare COID INT;

declare PSUOID INT;

declare PSUPOID INT;

declare NICOID INT;

declare NICPOID INT;

insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)

values(1 , SID, SLAB, now());

set SOID=Last_Insert_ID();

SELECT ObjIDClient.object_auto_id

FROM ObjIDClient

WHERE ObjIDClient.client_id = CID into COID;

SELECT ObjIDRack.object_auto_id

FROM ObjIDRack

WHERE ObjIDRack.rack_id =  RID into ROID;

insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)

values(26 , 0, 'PSU', now());

set PSUOID=Last_Insert_ID();

insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)

values(43 , 0, 'PSU Power Port', now());

set PSUPOID=Last_Insert_ID();

insert into objects (`object_type_id`, `device_object_id`, `label`, `date_created`)

values(16 , 0, 'NIC', now());

set NICOID=Last_Insert_ID();

insert into`object_objects` (`parent_object_id` ,  `object_id`)

values(ROID, SOID),(COID, SOID),(SOID,PSUOID),(SOID,NICOID),(PSUOID,PSUPOID),(20127,PSUPOID);

end

Ok i got your point maybe i wasn't clear enough though.What i want to do, is have one stored procedure which will insert data into only one table.This table should be passed into the procedure as a parameter and what i have to do there, is select some fields of this table a(ctually yes i wasn't clear enough at all,my apologies..).The actual problem is that i can't use the name of the table that was passed as a parameter in the from clause of my select.

and i can't use it either in the insert into clause....

ok thx a lot :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.