How to copy table data from another server?

Question

My server is running 2008 R2 and local PC is running 2008. I want to copy table records from database A on server to database B in local PC.

I tried:

INSERT INTO local.Customers.dbo.Info SELECT * FROM Server2.OldCustomers.dbo.Cust

but it gives error:

Cannot find Server 2 in sys.servers

Answer 1

Hi,

The easiest way to do this is by using the Import / Export Wizard; -

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

Also you can create a linked server; -

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

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

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

I hope this helps,

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood

Answer 2

Can you please explain us more..

1. Both Source and Destination tables are having same structure?

2. The data types in Source table(SQL Server 2008 R2) are compatible with the destination table(SQL Server 2008)

3. How many number of records you have in the source table? -- For performance check

---

Thanks & Regards Prasad DVR

Answer 3

1. Few column names are different.
2. Compatible types (only varchar)
3. Few hundreds

Answer 4

INSERT INTO local.Customers.dbo.Info SELECT * FROM Server2.OldCustomers.dbo.Cust

The above assumes that Server2 is defined as a linked server.

As stated above, easiest is the SSIS Import/Export Wizard:

http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

Answer 5

Hi,

The error you are getting is caused because you don't have a Linked Server set up.

You can also define different source and destination names within the Import / Export Wizard provided that your source and destination names are compatible. To do this you can click on the "Edit Mappings" button within the Import / Export Wizard.

How are you getting on with the advice / links I have already supplied?

Thanks in advance,

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood

Answer 6

@Kieran:

I am reading those links. BTW, what are the advantages of linked servers? Is it helpful only in mirroring?

Answer 7

Thank you for the feedback.

Linked servers are slightly more difficult to set up than using the Import / Export wizard. However Linked Servers are more flexible than using the Import / Export Wizard since the Import / Export wizard can only work source and destination at a time.

Once you have successfully set up a Linked Server you can write cross server queries in SQL similar to what you have already done in your initial question.

The Import / Export wizard can generate an SSIS package if you want it to, this package can then be extended within BIDS.

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood