Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

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?
0
dhenderson12
Asked:
dhenderson12
2 Solutions
 
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
 
Harish VargheseProject 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
SharathData 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
 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now