SQL Server - auto-populate field in one table with value from another table in another Database
I have two Azure SQL Server databases with the following as example: Database Name: DataProp Table Name: DataImports Columns: SearchID, SourceID, Text, Status, Country Database Name: Sources Table Name: SourceInformation Columns: SourceID, SourceTitle, Country Right now, the Country column in the DataProp database is all NULL. I need to auto-populate the Country field in DataProp with the values of the Country fields in the Sources database. The common field between the two tables is SourceID. I need to do this for all existing data, as well as have it occur for future records. What is the best way to accomplish this? A stored procedure that's set to run on a schedule? If so, I would appreciate guidance on the T-SQL syntax. As a side-note, I looked at the possibility of a computed column, but this will not work for us b/c we maintain an Azure Search Index on our tables, and Azure Search can't index computed columns.
I don't think you'll be able to directly write a join between tables in two different DBs. We had a similar problem and decided to move all tables into a single DB in separate schemas. I think in your case you can write a Webjob to pull in data from one table and update the second table. I also found one article related to this but haven't personally tried, so not sure if it works. https://ppolyzos.com/2016/07/30/cross-database-queries-in-azure-sql-databases/
The update statement should be pretty straight forward. Something like this. Update dp set Country = s.Country from DataProp dp join Sources s on s.SourceID = dp.SourceID Then if you need to run this on a regular basis just create a sql job with a single step. That step has nothing but a single stored procedure or even just this statement.
Update a table using temp table with select statement for multiple rows
PIVOT table in MS SQl server
Does Dapper request a full object from the database prior to Linq operations?
SQL Server to Postgresql - Database migration - Tool issues
SQL Agent Job to run .bat with gsutil rsync
Tool/script to clone SQL Server database with reduced rights (without backup functionality)?
SQL Server function with parameters
Bulk Load Data Conversion Error - Can't Find Answer
when i exec select on linux with odbc, hang on '__select_nocancel'
The system cannot find the file specified on Production server
An Error when trying to connect to SQL Server
Temporary table not created from dynamic query execution
How to process the data in the table, which is frequently inserted
Combine two columns and input the result in a different columns using SQL server
SQL Query to accept time difference