INSERT INTO... - 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 2005 Forums

Transact-SQL (2005)

INSERT INTO...

Author

Topic

cidr2
Starting Member

28 Posts

Posted - 2009-04-07 : 06:56:15

Hi there,

Hope I'm not being a pain, I have a problem. The database I'm working with is for staff. There are three tables involved (Employees, EmployeePositions, EmployeeRates)

Employees is related to the two other tables and EmployeePositions is not directly related to EmployeeRates.

I want to insert values into new rows in EmployeeRates using INSERT.
The two columns are HourlyRate and RateDate (being 01/04/09). However as there are different positions (Assistant Director, Administrator etc) I have to filter the rows by position from EmployeePositions and then insert the new rate that applies only to that position.

I’m finding it difficult to write this… is there anyone that could help me?

Thanks again.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-07 : 07:03:22

insert into EmployeeRates (HourlyRate,RateDate)
select <yourHourlyRate>,<yourRateDate>
from <yourRateTable> rt
inner join Employees e
on e.employeeid = rt.employeeid
inner join EmployeePositions ep
on ep.employeeid = e.employeeid
where ep.position in ('<whateverpositionyouwant>')

or you can do:

insert into EmployeeRates (HourlyRate,RateDate)
select
case when Poisiton = 'AsstDir' then <yourHourlyRate> when Poisition = 'Admin' then <yourHourlyRate> end as HourlyRate,
case when Poisiton = 'AsstDir' then <yourRateDate> when Poisition = 'Admin' then <yourRateDate> end as RateDate
from <yourRateTable> rt
inner join Employees e
on e.employeeid = rt.employeeid
inner join EmployeePositions ep
on ep.employeeid = e.employeeid

cidr2
Starting Member

28 Posts

Posted - 2009-04-07 : 07:22:05

Cheers for getting back to me RickD,

There isn't a RateTable, only the three tbles mentioned.
I made a mistake in my last message. The EmployeePositions can reference EmployeeRates with the employeeId.

Lets say there are 4 positions in EmployeePositions and I want to add new rows and subsequently new rates to EmployeeRates. The new rates for Admin should only be added to admin staff using the position from the other table as a filter. I'm not taking values from a rates table, I'm only adding new rows.

Apologies if I haven't explained this well.

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-07 : 08:24:08

Where are you getting the new rates from for each jobtype.

cidr2
Starting Member

28 Posts

Posted - 2009-04-07 : 09:11:27

I receive the rates from Management, not from a table. Each year the new rates are entered into the system.

Is there a way I could do this without the means of a rates table?

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources