sql-server


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.

Related Links

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?

Categories

HOME
sql-server
cakephp-3.x
ember.js
vb6
filterrific
ubuntu-16.04
label
formal-verification
travis-ci
goutte
eclipse-cdt
packer
outlook-web-addins
xul
data-synchronization
gwtp
symfony-forms
simple-injector
future
sql-tuning
ms-access-2007
primes
shippo
nmake
excel-2010
apple-tv
myob
excel-2013
system.data.sqlite
weinre
pygooglechart
superscript
gpib
picturebox
ipfw
oscommerce
color-scheme
ntfs-mft
rule
wsadmin
excel-interop
service-fabric-stateful
homekit
openweathermap
rapidweaver
aqgridview
recycle-bin
dimple.js
gist
sgmlreader
jenkins-jira-trigger
livescribe
azureportal
rhel6
cgcontext
mbaas
ruby-on-rails-2
rpostgresql
uicollectionviewlayout
festival
iostat
tomee
icefaces
debugdiag
wcf-ria-services
tomcat5
nofollow
selecteditem
picking
git-rebase
anythingslider
ipojo
castle-windsor-3
graphical-logo
ejabberd-saas
lexicographic
left-recursion
wic
angulartics
gridworld
backbone-relational
symphony-cms
appfog
device-emulation
fieldset
trailing-slash
lync-server-2010
sunspot-rails
windows-phone-7-emulator
makefile-project
driving-directions
ubuntu-11.04
selectonemenu
handwriting
server-error
sendfile
standardized
hardware-infrastructure

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App