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.
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Open in new window
Results:
Open in new window
With the normalized data, you can now MERGE data with table A:
Open in new window
Using OUTPUT allows you to audit what changed. For example:
Open in new window
Similarly (but in reverse), you can update table B from table A as follows.
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.