Newbie: Transfer table recors from source database to target database

[MEGUIA]

Hi,

I need to create a daily task that collect information from one database to another database. The source database would be used as an backend for an external asp.net application. Our clients would enter the required information then as a transaction task, I need to gather this information an inserted in our production database. The table structure of the two database are the same. I'm using sql server 2005. The integration would help me with this or I have ti create an script?.

I just want a starting point too get this done.

Any help would be appreciate.

Thank you.

 

[FlaBusInt]

If I understand your question correctly, you could do it either way.

Do you have to do any transformations or cleansing of the data as you shift it from one db to the other? If so, SSIS might be your better choice because it is usually faster to do those transforms in the data stream.

However, if you are just shoving data from one db to the other, then a T-SQL query or Bulk Insert might be your better bet.

 

[meguia1030]

Hi,

Thank you for your reply, If I choose SSIS, (pardon this question in advance) Could I create a daily task schedule and How can I do this?

Thank you

 

[FlaBusInt]

You would need to deploy the SSIS package to the appropriate server. Then you could schedule a job through Management Studio and set the Type as SQL Server Integration Services Package.

 

[arttoo]

Look at SQL Server Transactional Replication to get your new data accross. Easy to configure and mostly it just works!