The code example given in Query across multiple databases on same server will work for what I want to do. My only question is whether it's possible to include the name of the database in the SELECT statement for the records that are retrieved. In other words, I'd like to know if a record is from DB1 or DB2. Is this possible?
Asked
Active
Viewed 141 times
2
-
Just add it into each of the SELECT statements. – Martin Smith Feb 07 '18 at 22:54
2 Answers
2
This answer is based on the code from the accepted answer in the linked question (link).
Since you know while creating the View from which database you are pulling the data, you can add this information as a static column:
CREATE VIEW vCombinedRecords AS
SELECT 'DB1' as Database, * FROM DB1.dbo.MyTable
UNION ALL
SELECT 'DB2', * FROM DB2.dbo.MyTable
This will tag every record from DB1 with the value "DB1" in the Database column
rollstuhlfahrer
- 3,988
- 9
- 25
- 38
1
Sure, try:
CREATE VIEW vCombinedRecords AS
SELECT 'DB1' as Database_Name,* FROM DB1.dbo.MyTable
UNION ALL
SELECT 'DB2',* FROM DB2.dbo.MyTable
Scott Boston
- 147,308
- 15
- 139
- 187