Suggestions on how to update one database to another 1

[vamoose]

I have an issue with my IT department and would like to have some advice on what they are trying to tell me.

Let's say for example I have a MSSql 2000 database here at the plant in California which collects some 20,000 records per day. Each record has about 20 columns of various data.

Now I have a copy of the exact same database at the corporate office on their server in New York in which I want to send my daily data to every night at midnight then delete my daily data here in California.

1. Is this really such a big deal to make happen. They are trying to tell me that it's going to be a programming nightmare and will be even worse to try manage and update.

2. What would be the best or easiest way to make this happen. I would think some kind of Publish or Subscription function or perhaps just a simple insert into stored procedure?

I am a beginner SQL programmer but from what I have seen here and managed to accomplish so far this does not seem like such an issue to me as they are claiming it is. So before I tell them that full of it, I would like to be informed so I may have an intelligent response.

Please feel free to post your comments, suggestions and ideas. Thank you.

 

[Topaz70]

You have two options one is using DTS packages or another using Replication process.Chek under BOL online how use these process.

 

[mrdenny]

Replication won't work. Replication is great if you want to have the same data at both ends. In this case vamoose wants to delete the data from California after it's been transmitted to New York.

You have a few options on how to move the data.

1. Using a single stored procedure on the California server do something like this. (First create a staging table on the california server called NYStage or what ever you want to call it.)

create procedure usp_NYTransfer as
insert into NYStage
select *
from ProductionTable
where InsertDate between convert(varchar(10), getdate()-1, 101) and convert(varchar(10), getdate(), 101)

delete from ProductionTable
where KeyField in (select KeyField from NYStage)

BEGIN DISTRIBUTED TRANSACTION
    Insert into NYServer.Database.dbo.Table
    select *
    from NYStage

    if @@ERROR <> 0
    BEGIN
       rollback tranaction
    END
    ELSE
    BEGIN
       truncate table NYSTage
       if @@ERROR <> 0
           rollback transaction
       else
           commit transaction
    END

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com