Menu
Suppose that I have a database which name is testdb in test server. I also have a database named proddb in prod server.
Now I want to select data of a table of testdb database from proddb database.
How can I do that in SQL Server?
Also, I can do it using database link in oracle. But how can do that in SQL Server?
244
SELECT statements An SQL SELECT statement retrieves records from a database table according to clauses (for example, FROM and WHERE ) that specify criteria. The syntax is: SELECT column1, column2 FROM table1, table2 WHERE column2='value';
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
You need sp_addlinkedserver()
http://msdn.microsoft.com/en-us/library/ms190479.aspx
Example:
exec sp_addlinkedserver @server = 'test' then
select * from [server].[database].[schema].[table] In your example:
select * from [test].[testdb].[dbo].[table] In SQL Server 2012 and above, you don't need to create a link. You can execute directly
SELECT * FROM [TARGET_DATABASE].dbo.[TABLE] AS _TARGET I don't know whether previous versions of SQL Server work as well
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
