Using Baan 4GL/SQL, what's the most straightforward way of copying data from a table in one company to the same table in another?

For example, I want to copy a range of items in tiitm001 from company 160 to tiitm001 in company 161

Thanks,
Francis

Looks like Baan DLL tccomdll0025 has some functions for archiving data from one company to another. I should probably just make use of those?

Francis

There are a number of methods. Are the companies on the same VRC? There should be no table definition difference.

You can run Create Sequential Dump of Table (ttaad4226m000) and Create Table from Sequential Dump (ttaad4227m000), however that will give you the whole table.

Exchange would be very easy if you know how to use it.

One method that may work easily for you is in General Table Maintenance (ttaad4100). Look up the item master table for your source company and choose 'Y' (Continue) to view the records. You can then hit the space bar to get a drop down menu structure (Ascii Version of one anyway). Arrow over to 'Application' and you will see 'Export Rows'. Choose that, and it will ask for a range of items to export. You give it a directory - remember that directory. Then, exit (E), and look up the item master for the Target company, choose 'Y', and view the records from that company. Under 'Application', you will see 'Import Rows'. Choose that, give it the same path, and in they come.

This does not work if there are company numbers listed in the data you are trying to move, and it does not check for references, etc. But it's nice if all you need is the single table data unchanged.

Good luck.

Hi Francis,

I would prefer to the bdbpre and bdbpost commands they are easy to use and portable across companies.

Hi Francis,

If all the reference data exists in the target company for the records you want to copy, you can write simple 3GL code like this...



table ttiitm001

function main()
{
db.retry.point()
select tiitm001.*
from tiitm001 for update
where tiitm001._index1 inrange {:item.f} and {:item.t}
selectdo
tiitm001._compnr = 511 |Target Company
db.insert(ttiitm001,db.retry)
endselect
commit.transaction()
}


Note: Make sure you run this program in source company!!

Improvisation to Ravi's code -


table ttiitm001

function main()
{
db.retry.point()
select tiitm001.*
from tiitm001
where tiitm001._index1 inrange {:item.f} and {:item.t}
and tiitm001._compnr = 160 |* Source Company
selectdo
tiitm001._compnr = 161 |Target Company
db.insert(ttiitm001,db.retry, db.skip.dupl)
endselect
commit.transaction()
}


So you need not be in the source company and also the duplicate records are skipped.

Hi Prashanth,

Sorry to break this thread with a different question, but commit can be put before or after endselect. I would put it before, so:

What is better, and which difference occur?

Thanks

Hi Alejandro,

I would prefer to put the commit inside the select statements, than to do a mass commit at the end of processing and I might run into any buffer update errors.

I have written my own dlls which can import/export data (with the use of bdbpre and bdbpost command) from a table from one company to another, and I have built some programs which I handled multiple table data copying from company to another.

I think its a matter of choice and ease of which you like to use.

Mine is generic, that I can reuse again to built any new program, I just need to pass table name, company, path (for the creation of *.S files) as parameters.

Alejandro,

You can keep a counter so that for every say 100 records, you can commit. That will keep update buffer small and improves performace.

table ttiitm001

function main()
{
long c
db.retry.point()
select tiitm001.*
from tiitm001
where tiitm001._index1 inrange {:item.f} and {:item.t}
and tiitm001._compnr = 160 |* Source Company
selectdo
tiitm001._compnr = 161 |Target Company
db.insert(ttiitm001,db.retry, db.skip.dupl)
c = c+1
if (c>100) then
commit.transaction()
c=0
endif
endselect
if (c>0) then
commit.transaction()
endif
}

Also refer to the performance guidelines manual,

maybe you can use good options as -

"order by with retry" in case any commit fails, or "as prepared set" etc ...


Baan SQL supports the following options in relation to sets:

§ Maximum set size.

With this option you can indicate the maximum number of records the query can produce. The syntax is:

SELECT .... [from][where] AS SET WITH <number> ROWS

§ Prepared set.

With this option, the entire set is retrieved before the first record is returned. The set is temporarily stored. This option is useful when a process simultaneously selects and maintains (or deletes or adds) records. In this case, changes must not be visible in the selected records. The prepared set option forces a consistent read. The syntax is as follows:

SELECT ... [from][where] AS PREPARED SET

§ Maximum set size and prepared set.

You can combine both options as follows:

SELECT ... [from][where] AS PREPARED SET WITH <number> ROWS

If it works is good. Improve it, is better, but sharing improvements is the best.
Or something so...

By Folks.

Just wanted to thank y'all for the suggestions. We're going to try the compnr trick first. I didn't realize you could set it outside of the select statement itself.

We elected not to try export/import using bdbpre/bdbpost because some other calculations/table field value changes, etc. need to take place as well, along with wanting to select a limited set of items for copying.

For anyone who cares, the copying of items from one company to another is part of a bigger project that will hopefully let us effectively run MRP and MPS simulations. We don't have enough disk space for an entire copy of our production environment, so we're creating simulation companies that share most of the data with their production counterparts, except those tables that get updated by MRP and MPS, along with a few other tables as well. We'll create special users to run MRP and MPS in the simulation companies, and set their database permissions so they have read-only access to data stored in production companies. Hopefully, this approach will be viable...