July 22, 2008 at 5:11 pm
I would like to update a table on server B from server A every day. What would be the best approach to do instead of deleting all records from table on server B and inserting everything again.
I just want to update only those rows which are changed, that might also help me performance wise.
for instance right now am doing so..
delete from EmployeeJobs WHERE FiscalYr >= @Period
INSERT INTO EmployeeJobs
SELECT
[Emp_ID],
[FiscalYr],
[EmpName],
[SalAmount]
FROM
StateEmp WHERE FiscalYr >= @Period
July 22, 2008 at 5:22 pm
Hi Mike,
You could use a linked server to connect server B and server A. You can then query a table on server B using the following notation: serverB.databaseName.dbSchema.tableName (provided permissions have been setup sufficiently). Look up sp_addlinkedserver and sp_addlinkedserverlogin.
When doing updates you might like to consider INSERTs, UPDATEs and DELETEs. That is, if a new record is created on server B, do you want to create a corresponding new record on server A? Likewise, if a record is deleted from server B, do you want to delete this from server A? Update is the scenario you've mentioned so far. In SQL 2008 there's the new MERGE command which handles this (to my understanding, though I haven't used it). In SQL 2005 you need to write a SQL script for each. You might do them along the lines of:
Deletes: left outer join server A to server B and delete records from server A where server B is NULL
Updates: inner join server A to server B and update the relevant fields where there are changes to those fields
Inserts: a left outer join from server B to server A, inserting records where server A is NULL (do inserts last as there's no point updating and inserted record)
Hope this is enough to get you started 🙂
Sam
July 22, 2008 at 5:25 pm
You can also use the CHECKSUM function to determine if fields have changed. That is, you can compare CHECKSUM(serverA.col1, serverA.col2, serverA.col3) to CHECKSUM(serverB.col1, serverB.col2, serverB.col3) to determine if a row on server B is different to the row with the same key on server A. This would save you from comparing (serverA.col1 <> serverB.col1 OR serverA.col2 <> serverB.col2 etc).
July 23, 2008 at 5:22 am
Hi,
As it was pointed out rightly in the previous reply that a linked server can be used, also if it is not being used in any stored procedure and if you are running it manually once in a day, then I would suggest to create an SSIS package (DTS in SQL Server 2000) and import the table into a staging table and identify all the deleted, inserted and updated records and manipulate the data in your destination server database. You can then schedule the DTS to run once in a day.
Prakash.
---------------------------------------------------------------------------------
July 23, 2008 at 11:43 am
do i need to compare with CHECKSUM for each n every field like
WHERE
CHECKSUM(serverA.Column1,ServerA.Column2)
<>
CHECKSUM(ServerB.Column1,ServerB.Column2)
July 24, 2008 at 1:50 am
Hi Mike,
Basically the comparison you've suggested is saying "if any of the fields in Server A are different from any of the fields on Server B, then update the whole row". I'm sure there'd be debate over whether it's more efficient to write an update statement for every field (i.e. update fieldA where serverA.fieldA <> serverB.fieldA, update fieldB where serverA.fieldB <> serverB.fieldB). Sure, SQL Server won't need to write the value back for the fields which haven't changed, but it still needs to do the comparison (which it's doing using CHECKSUM). Coupled with that is a significantly increased amount of coding and maintenance compared to comparison of the whole row.
So to answer your question, I'd include all of the fields you want to check for changes inside the CHECKSUM function.
July 24, 2008 at 3:43 am
U can try this way,
Add one column Timestamp in Server A - Table, having datatype Timestamp ( binary format )
Add one column Timestamp in Server B- Table, having datatype binary
and fileterout the records when inserting rows in to Server B- Table Like
INSERT INTO Server B- Table
SELECT * FROM Server A- Table
WHERE Server A- Table.TimestampCol > Server B- Table.TimestampCol
so it will insert only new / updated records in Server B- Table.
July 24, 2008 at 12:21 pm
I am not supposed to add any columns in there.
July 24, 2008 at 12:56 pm
How about using
select * from ServerA
EXCEPT
select * from ServerB
I may get rows that are not matching in ServerA but how would I update rows in serverA from ServerB which are changed.
July 24, 2008 at 5:14 pm
Hi Mike,
I haven't used EXCEPT before but after looking it up it seems to do what you want by showing those rows from server A which are different to those rows on server B. It seems functionally equivalent to CHECKSUM(*) using my query above, but to use this result set you'd need to use it as a derived table (or store it in a temp table or something like that). For instance:
UPDATE ServerA.tableA
SET col1 = derivedTable.col1,
....
FROM ServerA.tableA
INNER JOIN (
SELECT * FROM ServerB.tableA
EXCEPT
SELECT * FROM ServerA.tableA
) AS derivedTable
ON ServerA.tableA.primaryKey = derivedTable.primaryKey
Although the CHECKSUM function takes some CPU time to generate, by this stage, I'd suggest it'd be more efficient than to using a derived result set for your update. If you really want to use EXCEPT, it'd be more efficient to reverse the EXCEPT as I've done above. Consider if you have a list of rows in ServerA which are different to those on ServerB; you still don't have the update values for those rows. You'd still need to join in ServerB.tableA based on the list of rows which have changed in tableA. If you reverse the query, but still use an INNER JOIN (as above) you have a list of rows which are different between ServerA and ServerB, the new values (from ServerB) and because it's an INNER JOIN it'll still only update those rows which exist on both ServerA *and* ServerB. I still think the CHECKSUM would be more efficient as you can do it without joining in a derived table. Perhaps you could do some performance tests both ways?
Sam
July 24, 2008 at 7:51 pm
you should consider binary_checksum()
* Noel
July 27, 2008 at 1:31 am
You can prefer replication to achieve this task.
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply