Bradley C Williams Bradley C Williams - 2 months ago 9
SQL Question

Microsoft SQL Query Help insert records based on template records. (Employees / Jobs)

I am no Expert but I am stuck trying to figure out the best method to accomplish my goals.
Problem:

Table of employees [EMPLOYEES] and table of allowed job codes [EMPTRANSRATES] both tables have a column called "filekey" its a 1 to many relationship.

I have several Template employees with an "IDNUM" that starts with "T" and many employees with "IDNUM" that are just numbers. These employees also have a crew identifier called "WG1". I need to insert into [EMPTRANSRATES] all of the records of the [EMPTRANSRATES] records of Template Employees with the filekey of any [EMPLOYEE] with a matching "WG1".

Proposed solution:

Use a cursor to build a list of filekey of employees with a specific "WG1" and in the cursor select into the [EMPTRANSRATES] table. - I dont like this solution because I will have to make one for each distinct WG1.

Question 1. Can I put a cursor in a cursor? For example generate a list of each distinct WG1 temmplate employee and in that select all employees file keys that have a matching WG1 and finally for each insert matching emptransrates with the unique file keys for each employee.

Question 2. Is there a better way to do this?

Thanks in advance!!!

Brad

EDIT

Example of data
[EMPLOYEES]
|FILEKEY|FNAME|LNAME|IDNUM|WG1|
1,Brad,Williams,1234,2
2,Template,Template,T2,2
3,Template.Template,T17,17
4,Stan,Smith,0531,2
5,Sally,Smith,9876,17

[EMPTRANSRATES]
|FILEKEY|JOB1|JOB2|
2,1,0
2,1,1
2,2,0
2,0,3
3,1,1
3,0,7

Expected results of script
[EMPLOYEES]
|FILEKEY|FNAME|LNAME|IDNUM|WG1|
1,Brad,Williams,1234,2
2,Template1,Template1,T2,2
3,Template2.Template2,T17,17
4,Stan,Smith,0531,2
5,Sally,Smith,9876,17

[EMPTRANSRATES]
|FILEKEY|JOB1|JOB2|
2,1,0
2,1,1
2,2,0
2,0,3
3,1,1
3,0,7
1,1,0
1,1,1
1,2,0
1,0,3
4,1,0
4,1,1
4,2,0
4,0,3
5,1,1
5,0,7


Because filekey 1 and filekey 4 were the same WG1 as the template1 employee new records were created for their filekeys in the emptransrats table copied from the matching template employee.

Because filekey 5 was the same WG1 as the template2 employee new records were created for the filekeys in the emptransrats table copied from the matching template employee.

Thanks again.

Answer

With the edit this is what I understand (thinking in sets)

  1. Select all templates (denoted as IDNUM starts with "T") from employee.

  2. Select all employees (denoted as IDNUM without "T") from employee table that have a matching WG1 number to those templates.

  3. Insert into EMPTRANSRATES those values that match the template but with the employees id.

Now that we understand it as operations on a set lets take it step by step.

Select all templates

SELECT *
FROM EMPLOYEES AS T -- T stands for Template
WHERE T.IDNUM LIKE 'T%'

Select employees with matching

SELECT *
FROM EMPLOYEES E
JOIN EMPLOYEES T ON E.WG1 = T.WG1 AND T.IDNUM LIKE 'T%'
WHERE NOT E.IDNUM LIKE 'T%'

Now before we can use this to insert we need the values from the other table, just join it:

SELECT *
FROM EMPLOYEES E
JOIN EMPLOYEES T ON E.WG1 = T.WG1 AND T.IDNUM LIKE 'T%'
JOIN EMPTRANSRATES R ON T.FILEKEY = R.FILEKEY
WHERE NOT E.IDNUM LIKE 'T%'

Done, just add in the insert:

INSERT INTO EMPTRANSRATES (FILEKEY, JOB1, JOB2)
  SELECT E.FILEKEY, R.JOB1, R.JOB2
  FROM EMPLOYEES E
  JOIN EMPLOYEES T ON E.WG1 = T.WG1 AND T.IDNUM LIKE 'T%'
  JOIN EMPTRANSRATES R ON T.FILEKEY = R.FILEKEY
  WHERE NOT E.IDNUM LIKE 'T%'

Please don't just copy the answer and run it. Instead run each step and make sure you understand what it is doing. Otherwise my bothering to answer this question and do it in a way that was clear was a total waste of time for both of us. This is fairly basic SQL and you should have been able to figure it out -- I hope the way I explained it gets you "thinking in SQL." Don't use cursors!

-------

Very hard to tell from your question but I expect you want something that looks like this:

INSERT INTO EMPTRANSRATE ({Columns to populate})
  SELECT DISTINCT {What you need to insert}
  FROM EMPLOYEES
  WHERE IDNUM LIKE 'T%' 
    AND CREW_IDENTIFIER = 'WG1'

If you are worried that the columns already exist in EMPTRANSRATE you can fix that like this (assuming columns need are A and B)

INSERT INTO EMPTRANSRATE (A, B)
  SELECT DISTINCT A, B
  FROM EMPLOYEES
  WHERE IDNUM LIKE 'T%' 
    AND CREW_IDENTIFIER = 'WG1'
    AND (A, B) NOT IN
        (SELECT A, B FROM EMPTRANSRATE)
Comments