transfer data across servers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

transfer data across servers

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-04-01 : 17:22:17

Hi,

Not sure thisis the right forum for my question.
I have to insert data from one table into another. The problem is that the 2 tables are on different servers and computers for that matter. I only need data that is in one specific table.
What is the easiest and most efficient way to do that.

If I access the 2 computers via remote desktop from my computer and actually go and copy the data from the table in computer 1 and paste it inside the table in computer 2 it does paste the data.However, for some reason it doesn't paste all the data. The column datatype is ntext.
I mean if the entry is for example 'hello world how are you today? blah bla blah'
It will only paste 'hello world how are'

I will appreciate your help.

Many thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-01 : 17:26:58

SQL server in not copy and paste. USe import/Export wizard if you have heard of it or check Books Online.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 17:32:53

I'd use a linked server or bcp.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-04-02 : 02:50:24

Hi,

I have tried exporting the data to excel but it doesn't work either. Throws an error all the time. Could it be coz it shows the datatype ntext as nvarchar (255) and the data in the ntext column is longer than 255? If yes, how can i solve this problem?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 04:47:55

You can use bcp,linkedserver,OPENROWSET or import/export wizard.
Try specifying a format file in bcp while exporting to excel to avoid the datatype mismatch errors. More details here:-

http://msdn2.microsoft.com/en-us/library/ms191516.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources