Im working on database synchronization in my app. It means I have 5 databases, but:
only in first database product could be added/removed/modified
this first database saving information about added/removed/modified product to table (with flag 1/2/3 as add/edit/remove and productID)
so first database generates INSERT script from SELECT, for example:
in my product_changes table (addedRemovedEdited INT, productID INT) I have information:
1, 15 (1 - flag means product with ID = 15 was added), or
2, 15 (2 - flag means product with ID = 15 was edited) etc.
Now using this information I can create script - and there is problem.
At this momment im creating scripts like:
SELECT (col1, col2, col3,...) FROM Product_Category;
string query = "INSERT INTO Table VALUES (@a,@b,@c)...";
SELECT (col1,col2,col3,...) FROM Product_price;
query += "INSERT INTO .......";
And I need to do it foreach tables which contains information about one single products. So for 10 products I'll have 10 * 12 (12 because there is ~12 tables about one product) blocks of code like INSERT INTO Table 1(....); INSERT INTO TABLE2(....).
Problem is also that, all data need to have same ID in every databases - so I'm using @@identity and put it into insert query. It has to be this way, because product with ID = 10 with name 'Keyboard' in mainDB = product with ID = 10 in DB10.
And the question - maybe some of you know any better (becouse that one is not so good) solution how can I create those scripts? Like query, which will take all information from my string[] a = {"Product", "Product_price", "Product_category"} tables and generate INSERT queries but - most important - where I can add @@identity.
@EDIT: I forgot. I found that solution: how i can generate programmatically "insert into" data script file from a database table?
Well, it does generate scripts, but with auto-incremented ID. And I need to add information in right order (as middle tables) for example:
INSERT INTO Product(.....) VALUES (...);
SET @pID = @@identity FROM Product;
INSERT INTO Price (priceID,.....) VALUES (...);
SET @prID = @@identity FROM Price;
INSERT INTO Product_price (priceID, productID,...) VALUES (@prID, @pID)
