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

Inhumanity

mssqltips.com/sqlservertutorial/202/… SQL Server Import and Export Wizard – Pentastich

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

Kadiyevka

Thanks for that sample, I'ved tried it, and I got this error: The OLE DB provider "SQL Server" has not been registered. Any idea? – Inhumanity

Answer 2

The answer given by Simon works fine for me but you have to do it in the right sequence: First you have to be in the server that you want to insert data into which is [DATABASE.WINDOWS.NET].[basecampdev] in your case.

You can try to see if you can select some data out of the Invoice table to make sure you have access.

Select top 10 * from [DATABASE.WINDOWS.NET].[basecampdev].[dbo].[invoice]

Secondly, execute the query given by Simon in order to link to a different server. This time use the other server:

EXEC sp_addlinkedserver [BC1-PC]; -- this will create a link tempdb that you can access from where you are
GO
USE tempdb;
GO
CREATE SYNONYM MyInvoice FOR 
    [BC1-PC].testdabse.dbo.invoice; -- Make a copy of the table and data that you can use
GO

Now just do your insert statement.

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 MyInvoice

Hope this helps!

Stormi

Answer 3

You cannot directly copy a table into a destination server database from a different database if source db is not in your linked servers. But one way is possible that, generate scripts (schema with data) of the desired table into one table temporarily in the source server DB, then execute the script in the destination server DB to create a table with your data. Finally use INSERT INTO [DESTINATION_TABLE] select * from [TEMPORARY_SOURCE_TABLE]. After getting the data into your destination table drop the temporary one.

I found this solution when I faced the same situation. Hope this helps you too.

Ful

can u pls explain with example how to do that ? – Yahwistic

Answer 4

USE [mydb1]

SELECT *
INTO mytable1
FROM OPENDATASOURCE (
        'SQLNCLI'
        ,'Data Source=XXX.XX.XX.XXX;Initial Catalog=mydb2;User ID=XXX;Password=XXXX'
        ).[mydb2].dbo.mytable2
    /*  steps - 
            1-  [mydb1] means our opend connection database 
            2-  mytable1 means create copy table in mydb1 database where we want insert record
            3-  XXX.XX.XX.XXX - another server name.
            4-  mydb2 another server database.
            5-  write User id and Password of another server credential
            6-  mytable2 is another server table where u fetch record from it. */

Arreola

Answer 5

You can use CREATE SYNONYM to remote object.

Judgeship

Answer 6

Worked for me...here ip will be remote sql server instance

insert into YourDatabaseName.dbo.TableName  select * from [192.168.9.230,1434].[SourceDatabaseName].[dbo].[SourceTableName]

Footcandle

Answer 7

If you have Visual Studio 2019 or later, you can easily do it by using SQL Data Comparison tool.

On Visual Studio, go to Tools > SQL Server > New Data Comparison.

You can select the source and target from different server. Choose what kind of data differences that you want to update. Next, you can choose which tables that you want to update.

Cuttlebone