TSQL Scenario

You have a table named Providers, which has two fields, ProviderCode and ProviderName.  You have a second table called Accounting with two fields AccountProviderCode and AccountPatientCode.  Write a single TSQL statement to generate a third table called Merged, combining the ProviderName from the Providers table with all the records and fields from the Accounting table.
brucegustPHP DeveloperAsked:
Who is Participating?
Assuming your "Merged" table exists and has columns in this order "ProviderCode, ProviderName, PatientCode", then I suspect the following sql query is what you want instead of a cross join... (if the column order doesn't match, just rearrange the columns in the select statement or add a clause onto the end of the "insert..." statement like this:  "(ProviderCode, ProviderName, PatientCode)" using the actual column names in Merged.)

   insert into Merged
   select B.AccountProviderCode, coalesce(A.ProviderName,'Missing!'), B.AccountPatientCode
     from Providers A
   right outer join Accounting B on B.AccountProviderCode = A.ProviderCode

Since you didn't state whether there was a FK relationship between the Providers and Accounting tables it is possible to have rows in the Accounting table with Providers that don't exist in the Providers table.  This query will show all rows from the Accounting table, match to the Providers table when it can, and indicate when it can't.

Technically, since the column names in Providers and Accounting aren't ambiguous (they're all unique), the aliases (A and B) aren't required.  However, it is good practice to specify them when multiple tables are involved if only for clarity.

insert into Merged
            A.ProviderName as ProviderName , B.*      
            Providers A
      cross join
            Accounting B
if, on the other hand, you know that there is exactly one AccountProviderCode for each ProviderCode, then the simplest query would be this:

 SELECT ProviderName, AccountProviderCode, AccountPatientCode
 FROM Providers, Accounting WHERE ProviderCode = AccountProviderCode
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.