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.
How to write SSRS report that compares end of month values?
SQL Server, first of each time series
Connection Waiting For SQL Remote Connection Freeze My App
Using BCP and xp_cmdshell with T-SQL inside SSMS. “The syntax of the command is incorrect”
SQL Server triggers to check credit card balance
How to use stored procedure to calculate DateTime difference in hours?
Loop through table by row T-SQL
Audit Microsoft SQL Server table with many columns
Select joining table based on condition
Linked server with same server name
How to group by two dates to sum an quantity
Retrieving record to a combo box in vb.net
Need select 2 rows from Table2, which is joined with Table1. See description
Can't add a connection in Visual Studio: no server name available
Average of a date column in sql [duplicate]
How to get the previous row value with an ORDERING BY DATE?