ansaurus

Question

Batch updation in SQL server 2008

Answer 1

+1 A:

For this to overcome you could use uniqueidentifier as the datatype for the branch ID.

So each database can create the ID with NEWID() without having to worry about the master database changing the key because is it universaly unique.

If you dont want to change the datatype you might have number ranges for each branch. Depending on the number of records that are created in each branch.

Yves M. 2010-09-28 06:57:54

thank you for your response... but using this ID how can i identify the previous transaction which was done in a branch corresponding to the id... Also the id in a branch and in head office may be different.

Nithesh 2010-09-28 12:56:22

Answer 2

+1 A:

The master server and DB is updated daily by batch updation

Not knowing the details of exactly how you do your batch updation or how big your database is and how many tables you have which face this kind of issue, one generic way that is possible way is by maintaining mapping tables

In your batch updation logic,

for new records, you will first insert the records into the master table and then using their new ID's generated from master, insert a record into mapping table with details like eg: branch, branchID, MasterID which stores the relation between the 2 ID's
for existing records, you will join with the mapping table and update the records using their masterID's from the mapping table

Usually, in SQL Server, if the batch updation is a job, you can use SSIS and have this kind of logic within your SSIS package.

Additionally, it depends on your design but there is usually a

Source DB --> Staging DB --> Destination DB

in your case which maps to

Branch DB --> Staging DB --> Master DB

for such ETL kind of tasks. In such cases, the mapping tables should probably reside in the Staging Database but if you do not have such a design (which you really should ) these mapping tables can reside in Destination i.e. Master database as well

InSane 2010-09-28 07:27:40

I have total 300 tables in which around 100 tables may face this problem.Can I do this for this much of tables , or any other method is there?.... @nithu@

Nithesh 2010-09-28 09:09:10

You could do this easily for 100 tables but this will be a major change to how your ETL package design works!! However, i dont see any easy way out of your predicament because it seems like quite a major design flaw - that you are generating ID's separately in all your separate databases. I would consider this suggestion to be more of a workaround but if you have the luxury, you may even consider an application redesign where you have ID generation in only 1 place. That would be actually the simplest way to take care of all your problems!!

InSane 2010-09-28 10:38:41

thank you... keep sharing knowledge.....

Nithesh 2010-09-28 11:18:40

Answer 3

+1 A:

I would forget about the technical aspects for now and clarify the business/application logic. It's not at all clear from your post where the conflicts can arise: on a customer record, on a transaction, on all data? Why do conflicts arise? How are conflicts resolved? When you understand exactly what's happening with the data and what the business expects to happen, then you can look into technical solutions.

Suggestions such as GUIDs, number ranges and mapping tables are very useful, but it isn't at all clear what problem you're trying to solve and what the business solution is. Getting all the data physically into one place is easy (SSIS would be a good place to start), the real question is what rules do you follow to merge the data?

Pondlife 2010-09-28 09:49:22

The conflict is occurs while am updating whole data into master data. EG: I have customer with id 5 in customer table, and a sales invoice by this customer with reference id 5(id of that customer). while I am updating both entry in to master db the customer id may be changed hence the sales invoice reference id (customer id) mismatch occures.... this occures many of the tables and transactions.....

Nithesh 2010-09-28 10:26:33

I don't understand why/how you need to change the IDs, but it sounds like you need some kind of mapping, e.g. CustomerID 5 is always mapped to 27 in the master system? A mapping table is easy to maintain, and you just join on it during updates or inserts.

Pondlife 2010-09-30 15:19:51

ansaurus

tags:

views:

answers:

Batch updation in SQL server 2008

related questions