Friday, January 18, 2013

TSQL - Using Like in a TSQL Join

More work importing data! This time for all of the tables I imported I needed to set a foreign key field for each record to indicate who created the record.

Each table that I imported the data from has a field called "Record Owner" that is the first and last name of the record creator plus an additional code. An example would be "Bob Smith 12345".

I have a table called "AppUser" that has primary key field "ID", and a "FirstName" and a "LastName" field. In my imported tables I have a field, "CreatedBy", that is the foreign key to the "ID" field in the "AppUser" table. Because of the additional code characters in the "Record Owner" field, I had to use a TSQL "like" to accomplish the join.

Here is my final TSQL query for populating the "CreatedBy" field:

UPDATE [INformV2].[dbo].[FoOfficeReportingPerformance]
SET [CreatedBy] = qbO.ID   
FROM [INformV2].[dbo].[FoOfficeReportingPerformance]
INNER JOIN [Importsv1].[dbo].[FoOfficeReportingPerformance] qbA
ON [INformV2].[dbo].[FoOfficeReportingPerformance].[QBRecordID] = qbA.[Office performance record ID#]
INNER JOIN [INformV2].[dbo].[AppUser] qbO
ON  qbA.[Record Owner] like (qbO.[FirstName] + ' ' +  qbO.[LastName] + '%')

Table "[Importsv1].[dbo].[FoOfficeReportingPerformance]" is the originally imported data.
Table "[INformV2].[dbo].[FoOfficeReportingPerformance]" is my final table. I imported data from "[Importsv1].[dbo].[FoOfficeReportingPerformance]" into this table. It has a field "QBRecordID" that matches the primary key, "Office performance record ID#", for the records in "[Importsv1].[dbo].[FoOfficeReportingPerformance]".

Anyway, what is interesting to me here is that you can use "like" as a comparisoin operator in a TSQL JOIN!

No comments:

Post a Comment

Thank you for commenting!