1

I have 2 servers named .05 and .06 . In both servers I have DB name Media but unfortunately I truncated a table name Media.Texas table in .05 server. But know I need to copy the same data from .06 to Media.Texas table in .05. I have done from one DB to Another DB but I never done between servers

What is the better way to code it to bring back the original Data from the other server.

insert into server2.database1.dbo.table1(
select * from server1.database1.dbo.table1)
Charles
  • 50,943
  • 13
  • 104
  • 142
Kumar
  • 65
  • 1
  • 7
  • possible duplicate of [How to update one table with data from another table in a separate database?](http://stackoverflow.com/questions/7434528/how-to-update-one-table-with-data-from-another-table-in-a-separate-database) – David Starkey Aug 16 '13 at 15:55

2 Answers2

0

Your options are:

Write an SSIS package to perform the extraction and publish to another server.

Configure replication to replicate the structure and data from one server to another - this depends on how often you need to ensure the tables match.

Create a linked server object to the destination server, ensure you have adequate access to the destination database, and write a query to copy across the data.

Export the data to a file and import into the destination table e.g. CSV, XML.

Alternatively, use a compare tool (there are various commercial tools out there e.g. RedGate) to script out the data you need and import it. The Microsoft Publishing Wizard tool can be used to script out the structure of a table along with data, and is suitable for small tables.

Mentatmatt
  • 515
  • 5
  • 13
  • I should add that I'm assuming you want to approach this task using database technologies rather than write some application code to do the task (such as a C# console application). – Mentatmatt Aug 16 '13 at 15:59
  • Msg 7202, Level 11, State 2, Line 1 Could not find server '162.148.140.06' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. – Kumar Aug 16 '13 at 17:04
  • You need to create a linked server object then you can use the name of that linked server in your queries. e.g. SALESSYSTEM as an alias then you would be able to do queries such as SELECT [id], [name] FROM [SALESSYSTEM].[Database].[dbo].[Sales] . Also, be sure to specify the instance sqlserver is running on for the server such as servername\default if you are running multiple instances or a non-standard instance name on the server you're connecting to. You can create linked servers in management studio under Server Objects -> Linked Servers. Right click for the option. – Mentatmatt Aug 19 '13 at 09:02
0

If creating a linked server is not an option, then you can use the built in import/export wizard in SSMS.

Right click on the database you want to copy to, then 'Tasks' -> 'Import Data'

Choose 'SQL Server Native Client 11.0' or other for your Data Source, and the rest of the wizard should be easy to follow.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • Msg 7202, Level 11, State 2, Line 1 Could not find server '162.148.140.06' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. – Kumar Aug 16 '13 at 17:03
  • Use the server name, not IP. – Hart CO Aug 16 '13 at 17:10