TSQL Scenario

Posted on 2005-04-14
Last Modified: 2010-04-17
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.
Question by:brucegust

    Expert Comment

    insert into Merged
                A.ProviderName as ProviderName , B.*      
                Providers A
          cross join
                Accounting B
    LVL 20

    Accepted Solution

    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.


    Expert Comment

    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

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
    A short article about problems I had with the new location API and permissions in Marshmallow
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now