Transfer tables between local and remote SQL servers

if you want it to be automatic, it can be a good idea to set a task in your PC or on the server.

If you have installed php on th server, then you can create a php script that will connect on both databases and copy the data. you can then test this php script by calling it through a web page. When it works, you can run the php.exe with the script as parameter. this will do the transfer.

you can also create a console software in .Net language.

I prefer two options
1) generate insert script file and upload to run on 2008-r2
2) generate csv file for each table and upload to use import wizard of 2008-r2

2) is the most simplest.

Step to create a csv file
1. Open SSMSE 2005
2. Execute sql command; SELECT * FROM table1
3. Right click on the datagrid and select 'Save Results As...'
4. Provide file name and location
5. Repeat step 2. for another tables

Thank you for your responses
Is the a way I can access the sql server on my  destop sql server from the dedicated server?
Dory

>Is the a way I can access the sql server on my  destop sql server from the dedicated server?
Yes, if you mean remote to you desktop screen and open SSMSE to connect your database, save as csv file and transfer file back to the server. All of these can be done by TeamViewer tool. It's free.

gnoon
I checked TeamViewer
It is free for 7 days only
Unfortunately I was looking for a solution that would more then 7 days.
Exporting & Imorting 4 csv files take too long. ... could you supply a script that will automate it?

cremel
Your solution looks intersting.
If I run the script on my desktop. Can I access the remote database without compromising security?

Thanks
Dory

on the remote database, you might be able to specify an account for a user with a specific IP or computer name for this entry.
This means your PC should be secured. But you should add a script on the server that contacts your database remotly.

gnoon
Thanks for your help
Dory