Greetings and other Salutations!
Yesterday I was working with someone new to T-SQL who needed to get data from one table in a database to another table in a different database. He was stumped and spent hours trying to find some way to do it. I was able to help him and realized this might be a common problem for people new to T-SQL.
We have two databases on our Instance SuperHero and Marvel and each database has a table Superhero.dbo.Character and Marvel.dbo.Bio.
First, prep the table you want to put data in. You won’t be using an insert statement for this task because in our scenario we already have data in our table. To prep my table I am adding a column at the end of my table and allowing it to have nulls.
CharacterID, Name, Universe, RealName(This is our new column that is null)
1, Spider-man, Marvel,
2, Batman, DC,
3, Iron Man, Marvel,
BioID, Name, CharacterID, RealName(This is where we will update our new column from)
1, Spider-man, 1, Peter Parker
2, Iron Man, 3, Tony Stark
Next, we join across databases. As you can see these two tables would join on CharacterID so:
SELECT C.CharacterID, C.Name, C.Universe, C.RealName, B.BioID, B.Name, B.CharacterID, B.RealName FROM Superhero.dbo.Character C INNER JOIN Marvel.dbo.Bio B ON C.CharacterID = B.CharacterID
The “INNER JOIN” also acts as my where clause so that it will only update records that match in both. Please always build and test your SELECT statement first to make sure it is going to update what you expect.
Now we build our update:
UPDATE dbo.Character SET RealName = B.RealName FROM Superhero.dbo.Character C INNER JOIN Marvel.dbo.Bio B ON C.CharacterID = B.CharacterID
Be sure you are running on the Superhero database for your update.
Who knew bringing super heroes together would only take a query?