How to access a data of table from another database in SQL Server?

How to access a data of table from another database in SQL Server?

Suppose you have two databases Production Database named Prod_db and Development database  named Dev_Db which are hosted in same SQL Server instance as shown below.

USE PROD_Db;

CREATE TABLE dbo.DataTbl
(Id INT IDENTITY(1,1),
Name VARCHAR(50),
Country VARCHAR(50),
CreatedBy VARCHAR(15)
)

INSERT INTO dbo.demo(Name, Country, CreatedBy)
VALUES('Joshep', 'US', 'Prod Admin'),
('Sanjay', 'India', 'Prod Admin'),
('Mark Hanery', 'South Africa', 'Prod Admin'),
('Suraj', 'India', 'Prod Admin'),
('Nadal', 'Canada', 'Prod Admin'),
('Jhong', 'Malaysia', 'Prod Admin'),
('KimSungLi', 'Japan', 'Prod Admin'),
('Ooi Tong','Japan', 'Prod Admin'),
('Sampan Singh', 'India', 'Prod Admin'),
('Cheong', 'China', 'Prod Admin')

As you can see, table named DataTbl is created in Prod_Db.

Now we have a table in Prod_Db, Lets copy the table data into another database.

Copying records from one database to another

Now we will access the data of table DataTbl which in Prod_db (consider as Source table) into Dev_Db (Consider as Target table).

To access the data from table which exists in different databases hosted in same SQL Server instance, you need to provide full qualify name for table such as table name including database name, and schema (database_name.schema_name.table_name) as shown below.

USE DEV_db;

SELECT * FROM Prod_Db.dbo.DataTbl

You can see, now you have accessed the database Prod_db table’s data into database Dev_Db.

Lets copy the this data into Dev_db’s  table.

Following Select into statemement copies the data of table DataTbl to the database Dev_Db in new table DataTblDev.

SELECT * INTO Dev_Db.dbo.DataTblDev 
FROM Prod_Db.dbo.DataTbl

Lets verify the records in table, and you can see data is copied successfully in table DataTblDev.

USE DEV_db;

SELECT * FROM dbo.DataTblDev

Copying only specified column’s data

In case if you do not want to copy the data for all columns, you can also specify the column’s name that you want copy in Target table.

Following statement copy only Id, Name, and Country data into Target table named DataTblDev1.

SELECT Id, Name, Country INTO Dev_Db.dbo.DataTblDev1 
FROM Prod_Db.dbo.DataTbl

Lets check the table DataTblDev1, and you can see data is copied into table and there are only Id, Name, and country column in table.

Also Read..

Select into select

Insert into select