INSERT INTO from two different server database

Question

I am trying to copy the data of testdabse.invoice table to basecampdev.invoice table. testdabse is a local database while basecampdev is in the server.

My query for copying data to another table doesn't work, it says

Invalid object name 'basecampdev.dbo.invoice'.  

I've been reading this documentation but find it hard to follow and understand.

These are the information given from the server

Server type: Database Engine
Server name: server.database.windows.net (this is not the real name)
Authentication: SQL Server Authentication
Login: myusername
Password: mypassword  

How can I connect to the server so that I would be able to run this query

INSERT INTO [basecampdev].[dbo].[invoice]
           ([InvoiceNumber]
           ,[TotalAmount]
           ,[IsActive]
           ,[CreatedBy]
           ,[UpdatedBy]
           ,[CreatedDate]
           ,[UpdatedDate]
           ,[Remarks])
SELECT [InvoiceNumber]
           ,[TotalAmount]
           ,[IsActive]
           ,[CreatedBy]
           ,[UpdatedBy]
           ,[CreatedDate]
           ,[UpdatedDate]
           ,[Remarks] FROM [testdabse].[dbo].[invoice]

Screen shot

Answer 1

It sounds like you might need to create and query linked database servers in SQL Server

At the moment you've created a query that's going between different databases using a 3 part name mydatabase.dbo.mytable but you need to go up a level and use a 4 part name myserver.mydatabase.dbo.mytable, see this post on four part naming for more info

edit
The four part naming for your existing query would be as shown below (which I suspect you may have already tried?), but this assumes you can "get to" the remote database with the four part name, you might need to edit your host file / register the server or otherwise identify where to find database.windows.net.

INSERT INTO [DATABASE.WINDOWS.NET].[basecampdev].[dbo].[invoice]
       ([InvoiceNumber]
       ,[TotalAmount]
       ,[IsActive]
       ,[CreatedBy]
       ,[UpdatedBy]
       ,[CreatedDate]
       ,[UpdatedDate]
       ,[Remarks])
SELECT [InvoiceNumber]
       ,[TotalAmount]
       ,[IsActive]
       ,[CreatedBy]
       ,[UpdatedBy]
       ,[CreatedDate]
       ,[UpdatedDate]
       ,[Remarks] FROM [BC1-PC].[testdabse].[dbo].[invoice]

If you can't access the remote server then see if you can create a linked database server:

EXEC sp_addlinkedserver [database.windows.net];
GO
USE tempdb;
GO
CREATE SYNONYM MyInvoice FOR 
    [database.windows.net].basecampdev.dbo.invoice;
GO

Then you can just query against MyEmployee without needing the full four part name