That's why I was suggesting the openrowset, because what you do is force sqlserver to make the inner join instead of coding it yourself (which is exponentially slower as you add records in the tables... and even slower because ado is object based and not sql based).

As for the execution method I'm really no expert in this field... I've just made a few dts to transfer databases objects and I never did a job. But I assume than once the sp or dts works it won't make much difference as it would be the same code executed by the same sql engine... (unless the compilation of the sp is more prepared than the dts compilation... but that would make for only a few miliseconds difference per execution). And I think the only to make it a nightly thing is to use a job to fire up the sp or the dts.

As for the update and insert query if you have a lot of rows to check for you'll have to do some gymnastics to make it fast.

first you'll need to create a table variable which is an exact replica (without the constraints) of the remote table like this :

Declare @RQ table (

PkObjSQL int NOT NULL primary key clustered,

xtype varchar (3) COLLATE French_CI_AS NOT NULL ,

id int NOT NULL ,

name varchar (128) COLLATE French_CI_AS NOT NULL ,

FkParent int NOT NULL ,

FkDB int NOT NULL ,

Colid int NOT NULL,

keyno smallint NOT NULL,

RefDate datetime NOT NULL,

Deleted bit NOT NULL,

IsPK bit NOT NULL,

Owner varchar (256),

indid smallint NOT NULL,

Length smallint NOT NULL,

isOutParam bit NOT NULL,

ColPrecision smallint NOT NULL,

VarTypeName varchar (16) NULL,

DefaultValue varchar (4000) NULL,

FkParentOBJ int NULL

)

--print 'insert the remote data to the table variable'

Insert into @RQ (PkObjSQL, XType, id, name, FkParent, FkDB, Colid, Keyno, RefDate, Deleted, IsPK, Owner, indid, Length, IsOutParam, Colprecision, VarTypeName, DefaultValue, FkParentOBJ)

(Select PkObjSQL, XType, id, name, FkParent, FkDB, Colid, Keyno, RefDate, Deleted, IsPK, Owner, indid, Length, IsOutParam, Colprecision, VarTypeName, DefaultValue, FkParentOBJ from OPENROWSET ('MSDASQL','DRIVER={SQL Server};SERVER=SERVEUR4'/*remote server name.. must be a linked server*/,'Select * from Documentation.dbo.ObjSQL' /*query*/) RQ)

--print 'check selected lines'

Select * from @RQ

--print 'Update statement'

Update dbo.ObjSQL set XType = RQ.XType, id = RQ.id, name = RQ.Name, FkParent = RQ.FkParent, FkDB = RQ.FkDB, Colid = RQ.FkDB, Keyno = RQ.Keyno, RefDate = RQ.RefDate, Deleted = Rq.Deleted, IsPK = Rq.IsPK, Owner = RQ.Owner, indid = RQ.indid, Length = RQ.Length, IsOutParam = RQ.IsOutParam, Colprecision = RQ.ColPrecision, VarTypeName = RQ.VarTypeName, DefaultValue = RQ.DefaultValue, FkParentOBJ = RQ.FkParentOBJ from dbo.ObjSQL UPD inner join @RQ RQ on UPD.PkObjSQL = RQ.PkObjSQL

/*this will only rewrite the lines that needs to be updated.. anywhere from 2 to 50 times faster depending on the data you are working with*/

and (UPD.XType RQ.XType or UPD.id RQ.id or UPD.name RQ.Name or UPD.FkParent RQ.FkParent or UPD.FkDB RQ.FkDB or UPD.Colid RQ.FkDB or UPD.Keyno RQ.Keyno or UPD.RefDate RQ.RefDate or UPD.Deleted Rq.Deleted or UPD.IsPK Rq.IsPK or UPD.Owner RQ.Owner or UPD.indid RQ.indid or UPD.Length RQ.Length or UPD.IsOutParam RQ.IsOutParam or UPD.Colprecision RQ.ColPrecision or UPD.VarTypeName RQ.VarTypeName or UPD.DefaultValue RQ.DefaultValue or UPD.FkParentOBJ RQ.FkParentOBJ)

--print 'Insert the new lines'

Insert into dbo.ObjSQL (XType, id, name, FkParent, FkDB, Colid, Keyno, RefDate, Deleted, IsPK, Owner, indid, Length, IsOutParam, Colprecision, VarTypeName, DefaultValue, FkParentOBJ) (Select XType, id, name, FkParent, FkDB, Colid, Keyno, RefDate, Deleted, IsPK, Owner, indid, Length, IsOutParam, Colprecision, VarTypeName, DefaultValue, FkParentOBJ from @RQ RQ

/*this checks that the line doesn't exists.. I use the Primary key but you can use whatever you need*/

where not Exists (Select PkObjSQL from dbo.ObjSQL COR where COR.PkObjSQL = RQ.PkObjSQL))

--print 'check the results'

--Select * from dbo.ObjSQL

I tested this script on 7000+ lines and it took less than 1 sec (somewhat fast server.. P4 2.4 ghz 512 mb of ram each).

However if all you need is to have one table to be the exact match of another table you can research replication (can't help you there.. but many other can help here), or you can also check log shipping (more for the whole database) but I never did that either.

Hope this helps.