Hello!
I am hoping that someone may be able to assist me with information on how to synchronize a table on my local server with a table on a remote SQL server.

Background Information:
I have a SQL server database hosted by a website hosting company which is my remote server. This database records information from my website and I need to get this information from the remote database table to a database table running on my local SQL server.

I have set up access to the remote server through the 'Client Network Utility' on my local server, and I have added this server as a 'linked server' through Enterprise Manager.

Currently, I am using a DTS package that executes every few minutes and drops the local table, re-creates it and populates it with a SELECT * FROM the remote server statement.

I don't like having to drop the local table and re-creating it every time as I really feel that this isn't the best way to be going about it. Plus I then have to copy ALL of the data across from the remote table to the local table each time.

I was wondering if there is some way to simply compare the remote and local tables and then update the local table with only the differences in the remote table.

Any help would be much appreciated.

Thanks :)

You may want to lookup information on replication on Books Online
Replication Options will give you a good reference on which type of replication will best suit your needs.

Hello,
Unfortunately I am unable to use Replication because the web hosting provider that runs the Remote server doesn't allow it. They informed me that I have to use DTS.
A real bummer because Replication is how I had originally planned to do it hence why I am now in a frenzy trying to figure out a similar solution using DTS.

this is from a another forum

Based on the discussion at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23038

and BSmith's idea (no relation), I came up with this silly stored proc to compare any two tables or views. Supply the table names and the columns you wish to compare and have fun!

The key to the GROUP BY approach is that it handles NULLS quite well which JOINS have trouble with.


CREATE PROCEDURE CompareTables(@table1 varchar(100), @table2 Varchar(100), @T1ColumnList varchar(1000), @T2ColumnList varchar(1000) = '')AS-- Table1, Table2 are the tables or views to compare.-- T1ColumnList is the list of columns to compare, from table1.-- Just list them comma-separated, like in a GROUP BY clause.-- If T2ColumnList is not specified, it is assumed to be the same-- as T1ColumnList. Otherwise, list the columns of Table2 in-- the same order as the columns in table1 that you wish to compare.---- The result is all records from either table that do NOT match-- the other table, along with which table the record is from.declare @SQL varchar(8000);IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnListset @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList + ' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' + @t2ColumnList + ' FROM ' + @Table2set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList + ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList + ' HAVING COUNT(*) = 1'exec ( @SQL)



If this does not work for you
Use exists or not exist to compare which rows are in one table
I would create a view to get the results that are or are not the the table you want to update

create view compare
as
SELECT DISTINCT column
FROM table1
WHERE NOT EXISTS
(SELECT *
FROM table2
WHERE table1.column = table2.column)

then insert the results to the table you wnat to update