transfer data from staging to production database

Question

I have production database with almost 500 tables, each table has an identity column and a timestamp column in addition to their normal columns, and I have a staging database which ia a copy of the production database in table structure but do have different data.
I want to transfer data to the production tables from the correspondind staging tables.

I can write insert statements for each tables, but this is a bit awkward.
Is there an easy way to transfer the data


thanks,

Answer 1

You can look at DTS or SSIS to perform the data transfer and automation. See Books Online for more details. You can also use BCP to export the data and import it. Lastly, you can simply setup a linked server from staging to production and pull the data via distributed queries. Each of these methods has their pros and cons, So it depends on your requirements.

Answer 2

I am using sql 2000 and do I have to create a data transfer task for eack table in the DTS package or can I do it with a single data trasfer task DTS package?

 

Thanks,

Answer 3

You may find one of the third party tools to be worth the cost.

 

I suggest exploring Red Gate's SQL Compare or ApexSQL's SQLDiff products. Both are relatively simple to set up use for tasks such as this.

 

Unfortunately, DTS doesn't have the capability to script out the data from all tables with a single command.

Answer 4

ttad wrote:

I am using sql 2000 and do I have to create a data transfer task for eack table in the DTS package or can I do it with a single data trasfer task DTS package?   Thanks,

 

 

you can also query the system tables and generate a dynamic sql to execute.. you may also need to use a cursor or some other cursor workaround.