How to copy table and data from one database to another database using SQL Server

Question

I have two database servers

one is server name: X and other one is server name: Y

I have a table ABC in both databases. I need to copy the data from X server, table ABC to the Y server, table ABC.

I tried this way its giving error.

INSERT INTO [X].Database.dbo.ABC SELECT * FROM [Y].Database.dbo.ABC

but I get this error:

Could not find server 'xxx.xxx.xxx.xxx' 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.

Have you run sp_addlinkedserver?

Dave
– Dave

2011-10-27 17:49:14 +00:00
Commented Oct 27, 2011 at 17:49 — Dave
– Dave, Commented Oct 27, 2011 at 17:49

Dylan Smith · Accepted Answer · 2011-10-27 17:12:44Z

2

Open up SSMS and point it to Server Y. Right click on database ABC, Tasks -> Import Data. Follow the wizard and point it at your source data in Server X.

answered Oct 27, 2011 at 17:12

Dylan Smith

22.3k2 gold badges51 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HLGEM · Accepted Answer · 2011-10-27 17:49:36Z

1

The error is telling you what to do. Use sp_addlinkedserver to create a linked server to the remote server. Then run the query.

answered Oct 27, 2011 at 17:49

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Comments

Tom · Accepted Answer · 2011-10-27 19:59:23Z

0

First exec procedure:

EXEC sp_addlinkedserver   
@server=N'Y', 
@srvproduct=N'',
@provider=N'SQLNCLI', 
@datasrc=N'Y\instance1';

Then run the query.

answered Oct 27, 2011 at 19:59

Tom

1042 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to copy table and data from one database to another database using SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related