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

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
Informatica powercenter
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

Categories

HOME
cakephp-3.x
sidekiq
elixir
project-management
itext
sed
voip
blockchain
travis-ci
appmaker
orientation
iis-7.5
docker-windows
currency
aruco
dropbox
pca
facebook-javascript-sdk
rfid
gatsby
xlsx
sign
scaling
session-timeout
device-detection
tweets
npm-install
scalajs-react
continuous-deployment
pptp
ab-initio
dpi
partial-application
google-api-dotnet-client
xor
ioc-container
read-write
canvasjs
modulo
service-locator
codesys
ntfs-mft
stdclass
powermta
uiviewpropertyanimator
rollback
component-pascal
windows-95
jexl
react-native-fbsdk
lotus
shinyjs
keycode
system.web.ui.webcontrols
mongoose-populate
software-product-lines
aescryptoserviceprovider
try-finally
pagekit
gherkin
license-key
test-class
intellitest
lmfit
retro-computing
findcontrol
rspec3
xaml-designer
mdm-zinc
subversion-edge
virus
lexicographic
django-1.6
belongs-to
qtestlib
sortable
node-imagemagick
android-looper
opendata
installshield-2011
multiple-login
objcmongodb
marmalade-edk
lync-server-2010
osi
blockingqueue
scrollto
makefile-project
flexicious
fileutils
querystringparameter
sendfile
nvelocity
querypath
surefire
openvg
swfloader
revision
outlook-form
msf
database-diagramming

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