Updating Records of Temporary Table

[JDTurk]

I need to get certain payroll deduction codes/amounts from our payroll history for employee# 123456789.
First, I want to get the employee’s pay dates from the payroll history table and write the records to a temporary table:

Create Table #tmp (
hr_pe_id Char(9),
hr_pe_name Char(30),
pyh_ck_dt Char(8),
retire_code Char(4),
retire_title Char(20),
retire_amt Int,
hrs_code Char(4),
hrs_title Char(20),
hrs_amt Int,
hrs_wrkd_code Char(4),
hrs_wrkd_title Char(20),
hrs_wrkd_amt Int,
hrs_rate_code Char(4),
hrs_rate_title Char(20),
hrs_rate_amt Int,
gr_earn_code Char(4),
gr_earn_title Char(20),
gr_earn_amt Int )
GO
Insert INTO #tmp(hr_pe_id, hr_pe_name, pyh_ck_dt)
select DISTINCT RTRIM(h.hr_pe_id), RTRIM(e.hr_pe_name), h.pyh_ck_dt
from pyh_hst_dtl h
inner join hr_pe_mstr e on e.hr_pe_id = h.hr_pe_id
where h.hr_pe_id = ‘123456789’ and h.pyh_ck_dt IS NOT NULL
GROUP BY h.hr_pe_id, e.hr_pe_name, h.pyh_ck_dt

Now my thinking is to read the first record of the #tmp table, get the deduction code/amount from the EmployeeDeductions table that matches the hr_pe_id and pyh_ck_dt of #tmp record, and update the record:

update #tmp
set
retire_code = (Select top 1 d.DeductionCode
from EmployeeDeductions d
where d.DeductionCode = 2100 and d.hr_pe_id = hr_pe_id
and d.pyh_ck_dt = pyh_ck_dt),

retire_title = (select top 1 c.py_cdh_cd
from py_cdh_mstr c
where c.py_cdh_no = 2100),

retire_amt = (select top 1 d.Amount
from EmployeeDeductions d
where d.DeductionCode = 2100 and d.hr_pe_id = hr_pe_id
and d.pyh_ck_dt = pyh_ck_dt)

I need to know how to open the #tmp table from the beginning, then read and update each record until EOF. Is this possible with SQL Server 2008? Thank you.


S8730 Processors (Redundncy/Mirror Img)
Aura 5.2 (Communication Manager)
Modular Messaging 5.2

 

[djj55]

For SQL server you would have to restrict update/select before it does not look at all the data. (See TOP)

Data is stored without regard to insert order. A clustered index will have data in an order (based on criteria) but the original table will not.

If you are trying to do a running total you will need to do a web search. A temporary table can be updated and changed, even indexed.

Hope this helps,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gk53]

So, you get data you need to temp table and need update a few fields in temp table? Of couse you can create cursor and loop throw each record in temp table, but it will be better to update from join something like

update #tmp
set retire_code = (Select top 1 d.DeductionCode
						from EmployeeDeductions d
						where d.DeductionCode = 2100 and d.hr_pe_id = t.hr_pe_id
						and d.pyh_ck_dt = t.pyh_ck_dt),
retire_title = (select top 1 c.py_cdh_cd
					from py_cdh_mstr c
					where c.py_cdh_no = 2100),
retire_amt = (select top 1 d.Amount
					from EmployeeDeductions d
					where d.DeductionCode = 2100 and d.hr_pe_id = t.hr_pe_id
					and d.pyh_ck_dt = t.pyh_ck_dt)
from #tmp t