How to access a data of table from another database in SQL Server?
Now you have a requirement to access a data from table in Prod_Db database into table in Dev_Db.
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
18,981 total views, 4 views today