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
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.
Secondly, execute the query given by Simon in order to link to a different server. This time use the other server:
Now just do your insert statement.
Hope this helps!
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.
You can use CREATE SYNONYM to remote object.
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 namemyserver.mydatabase.dbo.mytable
, see this post on four part naming for more infoedit
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
.If you can't access the remote server then see if you can create a linked database server:
Then you can just query against
MyEmployee
without needing the full four part name