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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if, on the other hand, you know that there is exactly one AccountProviderCode for each ProviderCode, then the simplest query would be this:
INSERT INTO Merged
SELECT ProviderName, AccountProviderCode, AccountPatientCode
FROM Providers, Accounting WHERE ProviderCode = AccountProviderCode
INSERT INTO Merged
SELECT ProviderName, AccountProviderCode, AccountPatientCode
FROM Providers, Accounting WHERE ProviderCode = AccountProviderCode
select
A.ProviderName as ProviderName , B.*
from
Providers A
cross join
Accounting B