Hi,

I am using db2 udb ese v8.1.4a on win2k platform.

I have a table with the following details:

TableA (Transactional table)
(Col1 int,
Col2 int
Col3 varchar(30));

TableA data:
Col1 Col2 Col3
1000 1000 Txt description
1000 2000 Txt description
1000 3000 Txt description
1000 4000 Txt description
1000 5000 Txt description
... ... ...
... ... ...
... ... ...
1000 9000 Txt description
1001 1000 Txt description
1001 3000 Txt description
1001 5000 Txt description
1001 6000 Txt description
1001 7000 Txt description
1001 8000 Txt description

TableB (Master Table):
(Col1 int,
Col2 varchar(30));

TableB Master data:
Col1 Col2
1000 Txt description
2000 Txt description
3000 Txt description
4000 Txt description
5000 Txt description
6000 Txt description
7000 Txt description
8000 Txt description
9000 Txt description

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.

Not sure if you are looking for the syntax of insert command or an automated way to insert all the missing entries in table A. Well, for simple straight insert you can use this:

insert into tableA values
(
1001, 2000, 'None',
1001, 4000, 'None',
1001, 9000, 'None'
);

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.

HTH

dollar

Hi dude,
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

HTH
brat.

Many thanks for your response.. Few explanations though.

* TableA.col3 = TableB.Col2 and it is static in nature.

* Simple insert statements (as shown below) doesn't solve the issue.

* 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.

Any help is greatly appreciated.

Hi,

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)

regards
brat.

Brat4 -- Thanx a ton for the quick response!!! Your solution was good enough - it worked. Data is getting migrated without any issue...

Regards.