INSERT INTO problem

Question

INSERT INTO problem

cidr

Ten Centuries

Points: 1353
More actions
April 7, 2009 at 6:32 am

#155520

Hi there,
I wonder if someone can help me. I'm not sure if this is an INSERT problem.
The db I'm working is for employees. There are three tables in question. Employees, EmployeeRates and EmployeePositions. The columns that matter are listed below.
Employees table
EmployeeName. . . . .EmployeeId
EmployeePositions
EmployeeId. . . . PositionId. . . .
EmployeeRates
EmployeeId. . . . Rate. . . . RateDate
Each of the tables contains the EmployeeId (Employees being parent) so they are all referenced to the Employees table. This aslo means the EmployeeRates and EmployeePositions can be referenced by this.
For simplicity sake, lets say there are 4 positions (Assistant Director, Administrator etc). The positions are in the EmployeePositions table as PositionId. Currently, I've been asked to put the new rates in to the system for all employees. There are about 400 employees and I'd rather do this with some kind of script. If, for example thee was 1 position per 100 people, I'd like to filter the 100 employees and add the new rows to these records within the EmployeeRates table.
Lets say the Administrators new rate is £25 hourly and started on the 1st April. I'd want to add a new row to EmployeeRates and add the new date and rate. This means I have to filter all employees that are only Administators; filtering it by using the PositionId from the other table.
I thought an INSERT could do this but now I'm not sure. I don't really know where to begin. When I try it I get errors. For example, "EmployeeId cannot be NULL".
Does anyone know how I would achieve adding the new rows with new values but still have the employeeID intact in the EmployeeRates table?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

If you can construct a SELECT which returns the rows which you want to insert into the rates table, then you're almost there. Here's a start:

SELECT p.PositionId, e.EmployeeId, [new rate], GETDATE()

FROM Employees e

INNER JOIN EmployeePositions p ON p.EmployeeId = e.EmployeeId

WHERE p.PositionId = [A position paying the same rate for all employees within it]

(where p.PositionId is retained for checking but would be removed)

You might need a derived table to handle multiple rows per employee in the rates table, returning only the most recent.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cidr Ten Centuries Points: 1353 More actions · Answer 2

Thanks Chris for getting back to me.

I've already done this. This is my problem. I don't know how to take the output which you suggest and add new rows along with the new date and rates.

I tried using what you suggest in the Where clause WHERE (SELECT etc.

Please can you elaborate how I may do this.

Thanks again.

Paul

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

INSERT INTO EmployeeRates (EmployeeId, Rate, RateDate)

SELECT EmployeeId, Rate, RateDate

FROM ...

Does the result set returned by the SELECT consist of all the rows (and no others) that you want to insert into EmployeeRates?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cidr Ten Centuries Points: 1353 More actions · Answer 4

I'm still not sure... There's something I'm not getting. The new dates and rates are handed to me by management and are not actually in a table within the db.

That's why I'm not really sure what to put into the FROM clause.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

pwatson (4/7/2009)
I'm still not sure... There's something I'm not getting. The new dates and rates are handed to me by management and are not actually in a table within the db.
That's why I'm not really sure what to put into the FROM clause.

FROM fagpacket f

Get them into a 'staging' table (a table used to hold transient data), how you do this will depend upon the existing format (Excel, paper). Once the data is in a staging table you can validate it using TSQL then upload into your production tables. If you provide more information about what management hand to you, we can suggest ways of loading it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden