insert a group of records based on another group of records

I have 2 tables:  1 is job table (jobId, jobName) and 1 is result table (rowId, jobId, resultCode).

There can be several resultCodes for each jobId.  I have created the resultCodes for 1 jobId, but I have 200 jobIds and I need to create the same resultCodes for each one.

how can I insert the resultCodes for each jobId that doesn't have one?
dhenderson12Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
check this.
INSERT table2 
SELECT t1.jobId, 
       t2.resultCode 
  FROM table1 t1 
       CROSS JOIN (SELECT DISTINCT resultCode 
                     FROM table2) t2 
 WHERE NOT EXISTS (SELECT 1 
                     FROM table2 t3 
                    WHERE t1.jobId = t3.jobId 
                      AND t2.resultCode = t3.resultCode) 

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
A sample data set would be helpful here.

>how can I insert the resultCodes for each jobId that doesn't have one?
Also, spell out in abundantly clear language which table you wish to insert, and what values, as it isn't clear in the question.
0
 
dhenderson12Author Commented:
ok.

table 1:
1  job1
2  job2
3  job3
4  job4
5  job5
6  job6

table 2:
1  job1  rc1
2  job1  rc2
3  job1  rc3
4  job2  rc1
5  job2  rc2
6  job2  rc3

now I have to create rc1, rc2, and rc3 for jobs 3, 4, 5, and 6.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Harish VargheseConnect With a Mentor Project LeaderCommented:
If you have a master table for ResultCodes and you want to insert all those codes for each of the jobs in Jobs table into Results table, you may use below query (I assume RowId is an identity column):
Insert into Results (jobId, resultCode)
Select J.JobId, RC.ResultCode
From Jobs J, ResultCodes RC
Where Not Exists (Select 1 from Results R
        Where R.JobId = J.JobId And R.ResultCode = RC.ResultCode)

Open in new window

If you do not have a separate master table for Result Codes and you want to setup the result codes for all jobs using the result codes you already setup for JobId 1, then you may use below query:
Insert into Results (jobId, resultCode)
Select J.JobId, RC.ResultCode
From Jobs J, Results RC
Where RC.JobId = 1 And Not Exists (Select 1 from Results R
        Where R.JobId = J.JobId And R.ResultCode = RC.ResultCode)

Open in new window

-Harish
0
 
dhenderson12Author Commented:
I don't understand the "select 1" statement.
0
 
SharathData EngineerCommented:
Without INSERT statement at line 1, did you try running the SELECT statement and get what you are expecting?
0
 
dhenderson12Author Commented:
yes.
0
 
dhenderson12Author Commented:
oh, select 1 and select * are the same.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.