help with stored procedure?

dkim18
dkim18 used Ask the Experts™
on
Hi,

Can you help with me this please?
I would like to know how to delete everything from this select statement and also insert them into another table.

if exists(Select empID FROM tblAvailableEmployee)
    BEGIN
       select a.AvalBusID from tblAvailableBus a inner join tblAvailableEmployee b on a.empID=b.empID

 //insert them into a table called tblOutBus
 then delete those records from tblAvailableBus
      END
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is this the type of thing you were looking for?

if exists(Select empID FROM tblAvailableEmployee)
    BEGIN
      ;with MyTargetedDeletes
      as
      (
      select a.AvalBusID as AvalBusID from tblAvailableBus a inner join tblAvailableEmployee b on a.empID=b.empID
      )
               INSERT INTO tblOutBus(AvalBusID)
               select AvalBusID from MyTargetedDeletes
               DELETE FROM tblAvailableBus
               WHERE AvalBusID in (select AvalBusID from MyTargetedDeletes)
  END
Commented:
select * into temptable from tblAvailableBus a inner join tblAvailableEmployee b on a.empID=b.empID

for delete 3 options

1) delete from tblAvailableBus a where a.empID in(select b.empID from tblAvailableEmployee b)

2) drop table  tblAvailableBus

3) delete from tblAvailableBus
I was thinking about this on the way in to work.  And I think of the capturing of the deletes more as an application audit.  And as I am not fond of allowing the foxes to guard the hen house, audits are the one function that I really prefer implementing withen databse triggers.  that way, they fire even if someone executes an ad-hoc query.


Somethinig fairly simple like..

Create trigger [dbo].[TrgDeletetblAvailableBus

]
on [dbo].[tblAvailableBus]
for DELETE
as
 
  insert into tblOutBus(AvalBusID)
  select AvalBusID from deleted
GO  
 

But I would add standard audit attributes to the table and my audit table would include an identity , insert datetime, user.  It would look more like..

Create trigger [dbo].[TrgDeletetblAvailableBus

]
on [dbo].[tblAvailableBus]
for DELETE
as
 
  insert into tblOutBus(AvalBusID,Deleted,DeletedBy)
  select AvalBusID,Getdate(),SYSTEM_USER from deleted
GO  

This covers cases 1 and 3
You can create a trigger on DROP table, but it is an AFTER trigger, so the table is already dropped when it fires. To audit the data in the table before the drop, you would put a rollback in the trigger, do the inserts to the audit table directly from the source table and the redrop the table.  I think it is better not to allow anyone to drop the table.  A DELETE FROM will clear the table and allow the trigger to fire with less overhead than a drop with the rollback. (my opinion - have not benchmarked...)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial