move selected data from one server to another sql server 2008

Question

i need to move selected data from 800+ tables in one database to the same 800+ tables in another database in another server. The data i select is based on date fields of every table. So, if i say table 1 date from 01/01/10 to 01/15/10, then only that data i want to be copied into the other server's database table specified. i hope i am not confusing anyone. what is easiest way to do this?

Answer 1

Look into SSIS. What you're talking about is very easy using it. Here is a page that talks about using variables in SSIS.

Answer 2

If this is a one time solution and the destination database is going to be a brand new one. I would restore a backup from the source database and then delete all the records outside of the date range I want in the new database.

If this is a one time solution and you need to move the data to an existing database you can use the export/import wizard in SQL Server Management Studio (This is not in Express edition). Right click on the database go to task and select export data. Then you can use a query to select the data based on the date range from the source table.

You can also link the servers and just run an insert into Server1.database.dbo.table1 to Server2.database.dbo.Table2.

If you will be moving data everyday I would recommend you to create an SSIS package. You can use the Export Wizard and save the SSIS package at the end. Then you can modify the SSIS package using Visual Studio.