Transfer tables between local and remote SQL servers

Hi Guys
I have a website located on a dedicated server
I need to transfer 4 tables from MS SQL 2005 EXPRESS on my desk to 2008-R2 webserver on my dedicated server.
In other words I often need to refresh (or replace) 4 tables on the 2008-R2
What would be the simplest way to do that?
Thanks
Dory
dory550Asked:
Who is Participating?
 
gnoonCommented:
Dory

After 7 days you can use most of its basic functions; Screen Remote Control, File Transfer. That's enough.
Here is a sample how to generate script what I mean

select 'insert into t(A) values('+isnull(''''+A+'''','null')+');' from t
-- output: insert into t(a) values('1');

but if A is a datetime field then

select 'insert into t(A) values('+isnull('convert(datetime,'''+convert(varchar,A,120)+''',120)','null')+');' from t
-- output: insert into t(a) values(convert(datetime,'2011-12-12 18:37:16',120));
0
 
tcremelCommented:
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.
0
 
gnoonCommented:
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
dory550Author Commented:
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
0
 
gnoonCommented:
>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.
0
 
dory550Author Commented:
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
0
 
tcremelCommented:
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.
0
 
dory550Author Commented:
gnoon
Thanks for your help
Dory
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.