0

How can I copy a table to another server. It's a linked server. I used this code, but it is not working. 

INSERT INTO [E-SVR].enrollmentCollege.CDDT_MSTR (position_id)
SELECT position_id FROM LETRAN.enrollmentCollege.CDDT_MSTR

Thank you.

Nozella
  • 11
  • 3
  • Elaborate on "it is not working"? Are you linking from to a remote SQL server? – sam yi Feb 09 '14 at 04:51
  • Take a look at this link... http://stackoverflow.com/questions/4091960/sql-server-linked-server-example-query – sam yi Feb 09 '14 at 04:54
  • I get this error. Could not find server 'LETRAN' 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. How can I do that? – Nozella Feb 09 '14 at 04:59
  • I under the impression... from your post that the linked server has already been created. Is that not correct? – sam yi Feb 09 '14 at 05:03
  • I thought it was already linked, but when I executed your given codes, I get that error so I assume it is not yet linked. – Nozella Feb 09 '14 at 05:05

3 Answers3

1

try this.

INSERT INTO [E-SVR].[enrollmentCollege].[dbo].CDDT_MSTR (position_id)
SELECT position_id FROM [LETRAN].[enrollmentCollege].[dbo].CDDT_MSTR
user3277522
  • 96
  • 4
  • Thank you, but I think "LETRAN" server name is incorrect because I get this error.. "Could not find server 'LETRAN' 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. " – Nozella Feb 09 '14 at 04:44
0

Try this.

INSERT INTO enrollmentCollege..CDDT_MSTR (position_id) SELECT position_id FROM LETRAN.enrollmentCollege..CDDT_MSTR

user3288802
  • 1
  • 1
0

When you are using a 4-part name for a table (which you have to so to use a linked server as part of the name), you need to specify the schema (the 3rd part) as well:

INSERT INTO [E-SVR].enrollmentCollege.dbo.CDDT_MSTR (position_id)
SELECT position_id FROM LETRAN.enrollmentCollege.dbo.CDDT_MSTR

If you want to use the default schema, you can leave the schema part empty:

INSERT INTO [E-SVR].enrollmentCollege..CDDT_MSTR (position_id)
SELECT position_id FROM LETRAN.enrollmentCollege..CDDT_MSTR
Szymon
  • 42,577
  • 16
  • 96
  • 114
  • I execute "select * from sys.servers" and get the server name "LETRAN" But, i still get this error.. "Could not find server 'LETRAN' 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. " – Nozella Feb 09 '14 at 04:43
  • OK, have you linked this server? – Szymon Feb 09 '14 at 04:44
  • I think not yet, I get this error. Could not find server 'LETRAN' 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. – Nozella Feb 09 '14 at 04:59