Set up DTS SQL 2000 1

[mttorpy]

I am using SQL server 2000 as a reporting server and would like to set up a DTS that runs every night to update about 20 tables. Currently I am dropping the tables and then just importing the new ones. The from sever name is NetMeter, the reporting server name is NetMeter_Test. Would I use append, update? What t-sql would it look like since I already have existing table with the correct names I just need to update them.

 

[JayKusch]

-- Copy data from NetMeter Server, DatabaseA, TableA all
-- the columns to NetMeter_Test Server, DatabaseA, TableA

TRUNCATE TABLE NetMeter_Test.TableA

INSERT INTO NetMeter_Test.DatabaseA.dbo.TableA

SELECT * FROM NetMeter.DatabaseA.dbo.TableA




Continue for all your other 20 tables.

Thanks

J. Kusch

 

[mttorpy]

That would automate what I am already doing and that is great but is there a way to just update the information I do not have in the Reporting DB? As to not have to recreate information I already had and have less of a load on the NetMeter Database.

 

[JayKusch]

Log Shipping, Replication, Backup/Restore databases if feasible are all options you may want to take a look at. If there is minimual data operations to the tables, you may be able to use triggers but this is not advisable, just listing all the options that come to mind.

Thanks

J. Kusch