How can I write a SQL select statement to include a lookup from another table?

Question

I'm copying data from one database to another and massaging the data while I'm at it. Both databases have tables called Clients and Jobs.

However, in database "Alpha" the Jobs table does not have a relationship to the Clients table, where database "Epsilon" does. Alpha's Jobs table just has the Clients name in an nvarchar column.

I need a select statement to lookup the Client's ID in the Client table by their name while I am inserting it into the Jobs table in Epsilon.

My unfinished SQL statement looks like this:

insert into Epsilon.dbo.Jobs (ClientId, Name, Location, DateCreated)
    select ????, Name, Location, DateCreated from Alpha.dbo.Jobs

How can I modify this so that the ???? contains the ClientId from the Clients table in Epsilon? I know I need to lookup the data using the Name column in Jobs, but I can't figure out the syntax for this.

Answer 1

insert into Epsilon.dbo.Jobs (ClientId, Name, Location, DateCreated) select c.ClientId, Name, Location, DateCreated from Alpha.dbo.Jobs as j inner join Epsilon.dbo.Clients as c On (j.ClientId = c.ClientId)

Answer 2

insert into Epsilon.dbo.Jobs (ClientId, Name, Location, DateCreated)
    select c.ClientID, a.Name, a.Location, a.DateCreated from Alpha.dbo.Jobs a
    join Epsilon.dbo.Client c on c.Name = a.ClientName

This is a pretty optimistic join, but even if it needs to be modified this should give you the general idea.

Answer 3

What you need is a join. Joins, contrary to what pretty much everybody thinks when starting out, don't require defined relationships in the schema of the database. They just require that the two columns you're comparing have the same type (edit see comments).

The question is which join do you want. Because there isn't a relationship defined, there may be clients that have jobs and clients that don't, and jobs that have clients and jobs that don't.

I'm assuming that you want all JOBS that exist, and where a ClientId matches the CLIENTS table bring in the ClientId, and where that relationship doesn't exist to leave the ClientId null. We can do this with a LEFT JOIN. Jobs LEFT JOIN Clients will bring in all records on the LEFT, even where the relationship defined with Clients on the right doesn't exist. We could reverse the two and do a RIGHT JOIN, but that's not what people usually do. I'll leave it to you to read up on other types of joins and how they work.

So your select statement would look like:

select ClientId, Name, Location, DateCreated 
from Alpha.dbo.Jobs as J LEFT JOIN 
    Alpha.dbo.Clients as C ON j.ClientName = c.ClientName

If Jobs.ClientName is not the same data type as c.ClientName, you can edit the schema before running the query to bring them in line with each other.