Lookup Query - 2 Tables

Question

Hi, I have two tables. dbo.audit and dbo.company. I am trying to pull the company name from my company table in to my audit table. I have created a column named Company_Name_Lookup in my audit table. I would like it to automatically perform the query each time a new audit is created.

• dbo.audit (Company_ID column, Company_Name_Lookup)
• dbo.company (Company_ID is PK, with a Company_Name column)

Bonus Question

From within Power Automate (formerly MS Flow), what query can I use to display the company name to test to see if I can perform the function there vs. having to perform the lookup in SQL.

Thanks for an insight!

Answer 1

You can do a one-time update using the below query

UPDATE t1
  SET t1.[Company_Name_Lookup] = t2.[Company_Name]
  FROM dbo.audit AS t1
  INNER JOIN dbo.company AS t2
  ON t1.[Company_ID] = t2.[Company_ID]
  WHERE t1.[Company_Name_Lookup] is NULL;

For subsequent records in audit table, you can write a trigger with similiar logic as above to populate the Company_Name_Lookup column.

However, this is not very efficient because you are duplicating the Company_name in your audit table even when you have a Company_ID column.

Where do you plan to use the Audit table?
How big (number of records) is this table expected to grow?
You will need to provide more details about your use case so that we can provide better suggestions.