Home » 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?

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.

Now you have a requirement to access a data from table in Prod_Db database into table in Dev_Db.

To demonstrate this, Lets create a sample table named DataTbl in Prod_Db and insert some records into table.

Here we have a table in Prod_Db 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.

 

Recommended for you

Select into select

Insert into select

 




 5,284 total views,  4 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.