Solved

insert a group of records based on another group of records

Posted on 2014-03-20
8
133 Views
Last Modified: 2014-03-22
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
Comment
Question by:dhenderson12
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39942786
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
 

Author Comment

by:dhenderson12
ID: 39942943
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
 
LVL 12

Assisted Solution

by:Harish Varghese
Harish Varghese earned 250 total points
ID: 39942950
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
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 39943589
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:dhenderson12
ID: 39945143
I don't understand the "select 1" statement.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39945390
Without INSERT statement at line 1, did you try running the SELECT statement and get what you are expecting?
0
 

Author Comment

by:dhenderson12
ID: 39947669
yes.
0
 

Author Comment

by:dhenderson12
ID: 39947689
oh, select 1 and select * are the same.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Database Containment - Benefits 6 35
Stored Procedure 2 47
Error in query 3 52
Linking a DMV to a database id/sql text in SQL server 2008 8 46
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now