StoredProcedure help

[kewldudehere]

Hi All,

I am migrating the data from one database to other database.
The tables are similar in structure in both databases.
After the initial migration,if any changes made in a table of database 1,these changes should be updated in similar table of other database.
I need to write a stored procedure that determines the changes made in the data of a table of one databse and load the changes in similar table in the other database.

So how to find the changes made in the data of a table.

[iburyak]

1. You can write a trigger that will instantly update, insert, delete records on other server.

If two servers are linked it is as easy as on the same server. If not let me know I will give you a solution on how to access one server from another.

2. If you have the same unique ID on both tables you can use it to insert\update\delete like this. Assume you have tables A and B


[PHP]delete from B where ID not in (select ID from A)

update B set
col1 = a.col1,
col2 = a.col2
from A a
JOIN B b on a.ID = b.ID

insert into B Select * from A where ID not in (select ID from B)[/PHP]

[mivey4]

1. You can write a trigger that will instantly update, insert, delete records on other server.

If two servers are linked it is as easy as on the same server. If not let me know I will give you a solution on how to access one server from another.

2. If you have the same unique ID on both tables you can use it to insert\update\delete like this. Assume you have tables A and B


[PHP]delete from B where ID not in (select ID from A)

update B set
col1 = a.col1,
col2 = a.col2
from A a
JOIN B b on a.ID = b.ID

insert into B Select * from A where ID not in (select ID from B)[/PHP]

What type of changes do you want to track on the second database table?
Inserts, Updates, Deletes?

Are you attempting to create permanent redundancy between both of the
systems? If so, you may want to explore other options.

To expand on what iburyak has already recommended here is an
Example of a Trigger for Inserts -

CREATE TRIGGER tr_trackInserts ON tableOne
FOR INSERT
AS
INSERT INTO Database2.dbo.tableTwo (columnOne, columnTwo, columnThree)
SELECT inserted.columnOne, inserted.columnTwo, insert.columnThree
FROM Database2.dbo.inserted
WHERE BLAH BLAH.... = BLAH BLAH
GO

Try this link and Research triggers:
http://www.sqlteam.com/item.asp?ItemID=3850

[kewldudehere]

Hi,

I need to keep the values sync in both tables .This means that when a mismatch is detected in phase 1,the new records in table 1(phase 1) will be migrated over table 2(Phase 2).

update tst_atr_sd_p2s.dbo.csdAttributeListDetail
set aldAttributeRef=a.aldAttributeRef,
aldAttrValueRef=a.aldAttrValueRef,
aldProductRef=a.aldProductRef ,
aldSeasonRef=a.aldSeasonRef
from tst_atl_sd1.dbo.csdAttributeListDetail a
inner join tst_atr_sd_p2s.dbo.csdAttributeListDetail b
on b.aldAttributeRef=a.aldAttributeRef and
b.aldAttrValueRef=a.aldAttrValueRef and
b.aldProductRef=a.aldProductRef and
b.aldSeasonRef=a.aldSeasonRef

tst_atr_sd_p2s is the phase 2 database and tst_atl_sd1 is phase 1 database.
The primary key in csdAttributeListDetail in this table is all 4 columns mentioned in query. And the table has approx 60k rows.When i do a join on the tables its taking much time.

IS there any other alternative way to do this in efficient way.

[iburyak]

You can write a trigger over table 1 which will update table 2 at insert\update time. This way while you update table 1 table 2 will be updated as well.

To speed up such small table just put index over primary key column.