Welcome to the new Deja Vu, you can populate a column from another database….

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.

Character
CharacterID, Name, Universe, RealName(This is our new column that is null)
1, Spider-man, Marvel,
2, Batman, DC,
3, Iron Man, Marvel,

Bio
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?

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s