Link to home
Start Free TrialLog in
Avatar of VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY

asked on

Complex SQL query

Table a:
ID SID SERVICES  PN  AU
1  100   AAA     0    1
1  200   BBB     1    1
1  300   CCC     0    2
2  100   AAA     1    1  

Table B:
ID  AAA BBB CCC DDD EEE FFF
1    5   2   3   4  5    6
2    9   8   7   1   8   2

Table c:

SID  NAME
100   AAA
200   BBB
300   CCC
400   DDD
500   EEE
600   FFF


Now i want to insert all values for the corresponding columns present in Table B to Table A in the following method.
Check if services is already in the table A then

if it's already then update the PN to 5 for ID 1 and SERVICES =AAA
Here resultant will be as below after the changing the value in table A.

ID SID SERVICES  PN  AU
1  100   AAA     5    1

ELse
it should insert a new record in the table A.
E.g
DDD,EEE,FFF services is not present for ID =1. So, it should insert a new record with PU as 0 and AU as the corresponding value.
So,result will be as

Table a:
ID SID SERVICES  PN  AU
1  100   AAA     0    1
1  200   BBB     1    1
1  300   CCC     0    2
1  400   DDD     0    4
1  500   EEE     0    5
1  600   FFF     0    6

Note SID will be taken from table C.

Like wise it should loop table B and fill up all missing values in the table B.
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Hi.

If you are dealing with a small list of values, such as the six you listed here, you can use PIVOT and UNPIVOT to help simplify this.  In addition, for SQL 2008 and higher, you can use MERGE to handle the UPDATE or INSERT in one statement.

Here is an example, using your sample data above.
DECLARE @tableA TABLE(ID INT, [SID] INT, [SERVICES] VARCHAR(10), PN INT, AU INT, PRIMARY KEY(ID, [SID]));
INSERT INTO @tableA(ID, [SID], [SERVICES], PN, AU)
VALUES(1, 100, 'AAA', 0, 1),
      (1, 200, 'BBB', 1, 1),
      (1, 300, 'CCC', 0, 2),
      (2, 100, 'AAA', 1, 1)
;

DECLARE @tableB TABLE(ID INT, AAA INT, BBB INT, CCC INT, DDD INT, EEE INT, FFF INT, PRIMARY KEY(ID));
INSERT INTO @tableB(ID, AAA, BBB, CCC, DDD, EEE, FFF)
VALUES(1, 5, 2, 3, 4, 5, 6),
      (2, 9, 8, 7, 1, 8, 2)
;

DECLARE @tableC TABLE([SID] INT, [NAME] VARCHAR(10), PRIMARY KEY([SID]));
INSERT INTO @tableC([SID], [NAME])
VALUES(100, 'AAA'),
      (200, 'BBB'),
      (300, 'CCC'),
      (400, 'DDD'),
      (500, 'EEE'),
      (600, 'FFF')
;

/* 
UNPIVOT demonstration. 
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
*/
SELECT u.ID, c.[SID], c.[NAME], u.PN_AU
FROM @tableB b
UNPIVOT (
    PN_AU
    FOR SERVICES_NAME
    /* Requires static list of column names. */
    IN (AAA, BBB, CCC, DDD, EEE, FFF)
) u
/* SELECT, or later JOINs, use UNPIVOT/PIVOT alias. */
JOIN @tableC c ON c.[NAME] = u.SERVICES_NAME
;

Open in new window


Results:
ID          SID         NAME       PN_AU
----------- ----------- ---------- -----------
1           100         AAA        5
1           200         BBB        2
1           300         CCC        3
1           400         DDD        4
1           500         EEE        5
1           600         FFF        6
2           100         AAA        9
2           200         BBB        8
2           300         CCC        7
2           400         DDD        1
2           500         EEE        8
2           600         FFF        2

Open in new window


With the normalized data, you can now MERGE data with table A:
/*
MERGE demonstration.
http://msdn.microsoft.com/en-us/library/bb510625.aspx 
*/
MERGE @tableA a
USING (
    SELECT u.ID, c.[SID], c.[NAME], u.PN_AU
    FROM @tableB b
    UNPIVOT (
        PN_AU
        FOR SERVICES_NAME
        IN (AAA, BBB, CCC, DDD, EEE, FFF)
    ) u
    JOIN @tableC c ON c.[NAME] = u.SERVICES_NAME
) b ON b.[ID] = a.[ID] AND b.[SID] = a.[SID]
WHEN MATCHED THEN /* UPDATE PN. */
    UPDATE SET a.PN = b.PN_AU
WHEN NOT MATCHED BY TARGET THEN /* INSERT AU. */
    INSERT (ID, [SID], [SERVICES], PN, AU)
    VALUES (b.ID, b.[SID], b.[NAME], 0, b.PN_AU)
OUTPUT $action, INSERTED.[ID], INSERTED.[SID], INSERTED.[SERVICES], INSERTED.PN, INSERTED.AU
;

Open in new window


Using OUTPUT allows you to audit what changed.  For example:
$action    ID          SID         SERVICES   PN          AU
---------- ----------- ----------- ---------- ----------- -----------
UPDATE     1           100         AAA        5           1
UPDATE     1           200         BBB        2           1
UPDATE     1           300         CCC        3           2
INSERT     1           400         DDD        0           4
INSERT     1           500         EEE        0           5
INSERT     1           600         FFF        0           6
UPDATE     2           100         AAA        9           1
INSERT     2           200         BBB        0           8
INSERT     2           300         CCC        0           7
INSERT     2           400         DDD        0           1
INSERT     2           500         EEE        0           8
INSERT     2           600         FFF        0           2

Open in new window


Similarly (but in reverse), you can update table B from table A as follows.
/* MERGE back data, or INSERT where ID does not exist, to table B. */
INSERT INTO @tableB(ID, AAA, BBB, CCC, DDD, EEE, FFF)
/* 
PIVOT demonstration. 
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
*/
SELECT ID, AAA, BBB, CCC, DDD, EEE, FFF
FROM (
    SELECT [ID], [SERVICES]
         , PN_AU = COALESCE(NULLIF(PN, 0), AU)
    FROM @tableA
) a
PIVOT (
    MAX(PN_AU)
    FOR [SERVICES]
    /* Requires static list of values. */
    IN (AAA, BBB, CCC, DDD, EEE, FFF)
) p
WHERE NOT EXISTS (
    /* WHERE filters also use UNPIVOT/PIVOT alias. */
    SELECT 1 FROM @tableB b WHERE b.[ID] = p.[ID]
)
;

Open in new window


If you need additional explanations beyond my code comments, please let me know.

Best regards,

Kevin

P.S. by default UNPIVOT will exclude rows for AAA, BBB, CCC, DDD, EEE, or FFF columns of table B where the value of the column is NULL.  If table B uses 0 or other default value, you may need to ensure you check for that on matches to ensure you do not overwrite valid data with some default.  This way, your table A will be the master.  Then you can safely overwrite table B with the PIVOT of table A.  Consequently, this is why I showed the update of table B after table A.  You always can switch this based on the need of your environment and how data flows.
So,result will be as
Table a:
ID SID SERVICES  PN  AU
1  100   AAA     0    1  => Shouldn't this be 1  100  AAA    5   1 in accordance with you 1st rule?
1  200   BBB     1    1
1  300   CCC     0    2
1  400   DDD     0    4
1  500   EEE     0    5
1  600   FFF     0    6

>>Like wise it should loop table B and fill up all missing values in the table B.<<
Can you explain this a little further? Perhaps what you would expect tableB to look like after the query.
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial