How to access a data of table from another database in SQL Server?
Now you have requirement to select data of a table1 of database1 from database2 of table2.
Lets look at an example, suppose you have following databases along with a tables as given below.
Database – Adventureworks, table – DimCustomer
Database – Sqltutorials, table – DimCustomer
Now you want to copy the data of a table –DimCustomer of database –Adventureworks to a table DimCustomer of Database – Sqltutorials.
Following statement selects the data of table – Dimcustomer from database –Adventureworks into Database – Sqltutorials.
use SqlTutorials Select * from Adventureworks.dbo.DimCustomer
Now will copy this selected data into a table of Sqltutorials database, lets modify the above query to achieve the requirement.
use SqlTutorials Insert into SqlTutorials.dbo.DimCustomer Select * from Adventureworks.dbo.DimCustomer
As you can see, it returns message saying 18484 rows affected which means data is successfully copied into target database’s table.
Now you can check table data executing below query.
Select * from SqlTutorials.dbo.DimCustomer