Multiple Appends in a Stored Procedure 1

[Krystoff]

Hello all,

I have just started learning about Output Parameters. I have a stored procedure that appends some data to table1. Then I want to get the identity of all the new records I just appended to table1 and append them to a another table along with some information from the first table.

I figured out how to get the last identity with @@identity but I can't get everything that I appended.

Is there a way to get this? Should I do it with a loop somehow? (append 1 record, get the identity, append to 2nd table, then append 2nd record and so forth)

Any help is appreciated!

Chris

 

[JohnDTampaBay]

How about getting the MAX(ID) from Table1 before you append data, then append data to Table1, then insert into Table2 from Table1 where ID > MAX(ID)?


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[philhege]

How about creating an INSERT trigger on the target table that inserts the data in the other table for you?

CREATE TRIGGER [trgi_prttable] ON [dbo].[prttable]
FOR INSERT
AS
INSERT INTO prtshipping(ei_fk_pt,ei_notes)
SELECT pt_pk, pt_notes from inserted

HTH

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[SQLSister]

I think Phil has a good idea here.

Hoever, I want to warn you to avoid using @@identity in the future. Use scope_identity instead. @@Identity returns the last identity value, so if you have a trigger which also inserts a record into a table with an identity that is the identity value you will get back not the one from the orignal table. Scope_identity() avoids this problem.

 

[Krystoff]

Thanks for all the quick responses!

Having just started learning SQL and so forth, I don't really know how to do complex stored procedures or triggers at all.

So if I understand correctly, Phil's advice will have my stored procedure do this:

User press's button, Append Stored Procedure runs (Can be mutltiple records) and appends into Table1

Trigger on Table1 sees that new records have been added to the table and then appends those new records into the second table, table2, along with the newly created ID for them?

If that is so then this will definately work. Let me know if I have something incorrect in my assumptions.

Chris

 

[Krystoff]

Ok, I tested it and it does append all new records into the second table! Woohoo!

Now, the question I have is can that trigger only be triggered when it comes from Table1? I have users inserting data into table1 all the time but I don't want that trigger being run except when the command button is pushed.

Is there a way to do that?

Chris

 

[philhege]

Triggers are pretty straightforward. If you insert something into a table, the INSERT trigger fires. Period. Maybe you could code to check for a condition (say, a flag field in a flag table) and insert based on the condition. However, you'd have to contend with contention:

1. Command button sets the flag
2. Another process inserts into the triggered table (table A).
3. Trigger checks for flag and inserts the other processe's info into table B. Trigger code sets flag to off.
4. The original process (command button) inserts into table A. The trigger fires, sees that flag is off, doesn't insert Command button's info into table B.

It's better to manage this in code (stored procedure) than a trigger.



Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....