Hi All,

Our client has acquired another company which is in same business. Now
they are planning to merge the data from new comapny's DB's  to
existing databases.

Problem is the difference between the schema of both the databases.
Database of new company has extra fields and some fields are even
allowing Null which is Not NULL in existing databases.

My question is that what is the right method to move the data from new
tables to existing tables.

We are planning to load the data into temporary tables and then
changins schema and finally moving the data to existing tables.

Is this the right approach or some other better technique.

Thanks,
NJ

This really does depend on the way the target system is set up. But here are
a few guidlines from my own experiences.

If the data isn't 'live and changing' and it's only for reporting on, then
there's little need to mash it into the target database - instead aggregate
the data into whatever reporting solution is being used.

If the data needs to be open to change by the target system, then things
become more difficult. Firstly you should keep in mind that this is a 'pull'
operation, not a 'push' - the data you end up with is dictated by the target
database. You need to identify a source for each field of each table which
cannot or should not be null along with others which are desirable. Then look
and see if the places that you've had to enter a constant or some fudged bit
of data would cause errors or strange results in select statements -
generally check (on a test system, of course) if the transfer effects the way
the database works currently.

The target system may have the facility to import data from xml or csv. If
it's possible to import data through a GUI (which will enforce all
constraints on the data) it should be done this way.

Mostly, you'll spend a lot of time carrying out trial runs on test systems
and seeing if you broke it.

In most cases, though - if the data is only going to be for reporting
purposes and it won't be changing (historical data only) then just add the
data to a new database which you can reference in your reporting solution.

Hope that helped a bit.

Show quoteHide quote

Re-reading your post, it might be that the new company's data may not need to
move. Are you wanting to merge the management of the data, or just looking
for cross company reporting? It makes a big difference.

Show quoteHide quote

My client is looking for consolidation of data in one database rather
than having multiple database as nature of data is same in both he
companies barring few change in the table schema. So they want to merge
new data with already existing data.

We will be getting the new company data in excel sheets then we need to
perform the conversion as needed for merging with existing data and
finally merging.

Thanks,
NJ

rocket salad wrote:
Show quoteHide quote

neeju (njvds.***@gmail.com) writes:
Show quoteHide quoteThere are two questions here. The first is: what do you want the final
result to be? Do you want their extra fields in your database, or do you
want to scrap them? What to do with the columns where the other company
has NULL where you have NOT NULL? Should you change to NOT NULL? Ot you
should you fill their NULL values with "N/A" or similar?

This is a business decision, and we cannot answer that question in this
newsgroup.

The other question is: how to perform the merge technically? The answer
to that question is "it depends". It sounds as if both companies use
the same product, but just different incarnations of it. Also, since
you don't mention it, I assume that both databases are on SQL Server.
In that case, I would get a copy of their database to the same server
as the databases they are to be merged into, then run any scripts needed
to fill in missing NULL values, adjust ids to avoid collisions etc, and
then just INSERT the data over. I don't really see the need to change
the schema in a database which is to be abandoned anyway.

And, oh, things you may have to look for is that you may have things
that exists in both databases, for instance customers. You may want to
avoid adding the same customer twice. But that's another business decision.

SET/SELECT truncate string
parse out set of numbers from a column
stored procedure stopped working
How is the design for this complex requirement ?
trying to use a cursor to update table..