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

User · Answer

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

User · Answer

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

User · Answer

You can use CREATE SYNONYM to remote object

User · Answer

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

User · Answer

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

[sql] INSERT INTO from two different server database

Examples related to sql

Examples related to sql-server

Examples related to openrowset