0

I found this question Copy table to a different database on a different SQL Server which is close to what I want but my two databases are on two different machines. I am interested in backing 1 or two tables, not the whole database. I tried BCP backup and bulk insert but I am consistently getting error on importing date field (type mismatch or invalid character for the specified codepage). I gave up after I successfully imported the peice of csv file that I was getting error for in a new test table.

Now I would like something like this

select INTO mycomputer\SQLEXPRESS\target_table from ReMOTECOMPUTER\SQLEXPRESS\source_table

or anything similar? Can I do that, what is the proper syntax if yes. I tried but was not successful.

Community
  • 1
  • 1
TheTechGuy
  • 16,560
  • 16
  • 115
  • 136

1 Answers1

1

Have you looked at using linked servers? We had a somewhat similar data consistency issue and used a linked server setup to provide for triggered data propagation. Once you have the linked servers defined you can issue your statement pretty much as you have it listed in your question.

http://msdn.microsoft.com/en-us/library/ms188279.aspx

Carth
  • 2,303
  • 1
  • 17
  • 26
  • yes I have found my data is not consistent and therefore can not be imported using bulk import. Database backup does work fine though. Link Server is new to me, will take a look. Thanks – TheTechGuy Oct 24 '11 at 20:47