TableA contains transactional data sets.
TableB contains master records which are static as shown above.
Now the issue: In TableA for a single col1 value may contain values from master table i.e 1000 through 9000. Now I need to populate TableA with those missing records i.e for Col1 = 1001, I am missing master record entries for 2000, 4000 and 9000.
My question is how do we make an entry into TableA for those missing record entries using SQL or otherwise. eg: In above case, I need to insert three rows in TableA with values
1001, 2000, 'None'
1001, 4000, 'None'
1001, 9000, 'None'
Appreciate your help. Please treat this as urgent. Thanks in advance.
and so on....for an automated way...you need to find out which column1 values do not contain 2000, 4000 and 9000 and use insert statement above. If they all do no contain those values then it's even simpler and the solution will depend on how many rows you will have to insert.
I believe this is possible through many way, one way which i came out after reading ur post was this.
1. first create a temp table(i will refer it as temp_table) with 2 columns (same as col1 of transaction and col1 of master table). The order when creating the table should be transaction column first and then the master column
2. run the following query
insert into temp_table select distinct a.col1,b.col1 from transaction a,Master b
*This will get "all" combinations of transactions against each of the master in the temp_table.
3. Alter table transaction to make col1,col2 as composite primary key.
*This is important, to avoid duplicate entries in transaction.
4. export from temp_table into ixf or csv file
5. import into transaction table from ixf/csv file.
*Because of the composite primary key in transaction table, duplicate entries will not be loaded and the rest will successfully be inserted.
Note: If you need to insert values to the third column of transaction table then you can create the temp_table with 3 columns instead of 2 and change the query in step 2 as following
insert into temp_table select distinct a.col1,b.col1,'some text' from transaction
The only drawback to this method is the values of the third column need to be static. From your post I couldnt understand what kind of data would be there in column3
* Brat4: I agree with your approach, I am constructing the temp table to process the same. But, no where close the result...
The whole point is, the user can have any combination of entries in transactional table for instance: for a single value of col1 (1001 or any other id as generated by the application) he can have 9 possible values (coming from master table which is static in nature). However, it is not mandatory to have all the 9 records...so, our task is to find out which one of those record are missing from the combination and insert the same into TableA.
Maybe I didnt percieve ur requirement .. .. I'll just iterate what i understand .
"1. transaction table contains many values in col1 -dynamic.
2. transaction table contains col2 - from master table
3. master table contains 9 records in col1
4. transaction table has col2 which MAY OR MAY NOT have all the combination of each of transaction.col1 with a value of master.col1
Requirement: transaction table needs to be updated with the combination of each transaction.col1 along with each of master.col1
am I right in the above?
If yes read the following , if not corect then I'd require hmmm a little more detail
Currently the transaction table has any combinations (tarnsaction.col1 along with master.col2) and to push in the missing combinations, the method I described below should work.
The temp table (using the query in step 2) will contain all the possible combinations (including the ones already exisiting in the transaction table). When you import it Using your db2 control center or clp then the rows which are already exisitng would be rejected due to the constraint as described in step 3 As a result, all the missing entries will be inserted.
But if you need to run it as a regular job, then maybe you could do it with a combination of a OS cron job (which imports into transaction table from a specific file generated by a stored procedure (which populates the temp_table and exports the ixf file)