CODEWITHSUNDEEP
sql-servert-sql
Mysterei
Mysterei

Reputation: 33

Merge data from multiple source tables into a single destination table

I am trying to write a stored procedure that converts the contents of one table to another, however I have no idea where to begin. In my scenario, I have 4 tables: [Source], [Type_A], [Type_B] & [Destination].

[Source] has 2 columns whose values need to be carried over to [Destination]. One of the columns in [Source] is "type" (a discriminator).

If "type" is 0, then I need to pull a subset of columns from [Type_A] & add a record to [Destination] with some static values added.

If "type" is 1, then I need to pull a subset of columns from [Type_B] & add a record to [Destination] with some different static values added.

There will be a variable number of records handled per execution of this stored procedure, which will be invoked from an ASP.NET app by the user.

I am a relative newb in SQL, so forgive me if the solution is a simple one. I know that I can do this iteratively in code (C#), but I would rather avoid the overhead of multiple DB calls & would like the whole thing wrapped in a transaction.

If anyone can shed some light on how I could accomplish this, I would really appreciate it.

Thanks!

Upvotes: 1

Views: 1119

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

Assuming that you have relationships between Source, Type_A and Type_B:

INSERT INTO Destination
    (Col1, Col2, Col3, Col4, Col5)
    SELECT s.Col1, 
           s.type, 
           /* The next two columns illustrate choosing a  */
           /* subset from either Type_A or Type_B         */
           COALESCE(a.Col3, b.Col3),
           COALESCE(a.Col4, b.Col4),
           /* The next column illustrates choosing a      */
           /* static value based on Source.type           */
           CASE WHEN s.type = 0 THEN 'Static Value 1'
                WHEN s.type = 1 THEN 'Static Value 2'
                ELSE NULL
           END /* CASE */
        FROM Source s
            LEFT JOIN Type_A a
                ON s.Col1 = a.Col1
                    AND s.type = 0
            LEFT JOIN Type_B b 
                ON s.Col1 = b.Col1
                    AND s.type = 1

Upvotes: 2

Related Questions