update and insert problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

update and insert problem

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-25 : 04:12:49

I have 2 tables

The 1st table emp_details contains all the details and there are emps with pass and failed.Active is a field which determines which students have been taken for special assessment

emp_details

emp_id Dept_id Passed Active
2356 Sc Y 0
4567 Sc N 0
6457 sc Y 0
5688 Sr Y 0
4234 Sc N 0
6793 Sc N 0
5721 MA N 0
1279 EN Y 0

Now I need a query which says select 5 passed(Y) emp_ids and 5 failed emp_ids(Passed =N)may be top 5 of each category and then make the field active by updating the field Active to 1 which shows that the ACtive=1 students are selected.

Now the second part.As soon as the update is done with Active = 1 a trigger should be there where those emp_ids and the passed field data should be inserted to a new table called Active_emps as shown below.

Active_emps

emp_id Passed
2356 Y
4567 N
6457 Y
5688 Y
4234 N
6793 N
5721 N
1279 Y

This process is a continous process where the next top 10 or 20 emp_ids will be selected which are not active = 0 and then updated to 1 and then that corressponding datas are inserted.Please help with this queries

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-25 : 06:15:22

No point in a trigger
declare @t table (emp_id int)
insert @t select top 5 emp_id from emp_details where Passed = 'Y' and Active = 0
insert @t select top 5 emp_id from emp_details where Passed = 'N' and Active = 0
begin tran
insert Active_emps
select e.emp_id, e.passed
from @t t
join emp_details e
on e.emp_id = t.emp_id

if @@error <> 0
begin
raiserror('failed', 16, -1)
rollback tran
return
end

update emp_details
set Active = 1
from emp_details e
join @t t
on e.emp_id = t.emp_id

if @@error <> 0
begin
raiserror('failed', 16, -1)
rollback tran
return
end

commit tran

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources