Write Update sql query for Denomalized table to normalized table 1

[sqldevrequiretektips]

Hello Everyone..

I have ask this question to many SQL Developer but nobody had given me better answer...

On my form i have button save/upload History when user clicks on it, I want to save the data from WLCust_Denormalized to WLCustAct_Emp table...

How would i save the data from WLDenormalized table to tblWLEmpType_WLDay


For more detail see structure and data...

For example....
WLDenormalized table structure and sample data..

Field Names Records
CustLifeNo, 330374
WeekNo, - 1
MON_Act_M,-- M
MON_Act_O -- NULL
MON_Act_D -- D
MON_Emp_C -- C
MON_Emp_F -- NULL
MON_Emp_P -- NULL

TUE_Act_M -- NULL
TUE_Act_O -- NULL
TUE_Act_D -- NULL
TUE_Emp_C -- NULL
TUE_Emp_F -- NULL
TUE_Emp_P -- NULL

WED_Act_M -- M
WED_Act_O -- O
WED_Act_D -- NULL
WED_Emp_C -- C
WED_Emp_F -- NULL
WED_Emp_P -- NULL

THU_Act_M -- NULL
THU_Act_O -- NULL
THU_Act_D -- NULL
THU_Emp_C -- NULL
THU_Emp_F -- NULL
THU_Emp_P -- NULL

FRI_Act_M -- M
FRI_Act_O -- NULL
FRI_Act_D -- NULL
FRI_Emp_C -- C
FRI_Emp_F -- NULL
FRI_Emp_P -- NULL

SAT_Act_M -- NULL
SAT_Act_O -- NULL
SAT_Act_D -- NULL
SAT_Emp_C -- NULL
SAT_Emp_F -- NULL
SAT_Emp_P -- NULL

SUN_Act_M -- NULL
SUN_Act_O -- NULL
SUN_Act_D -- NULL
SUN_Emp_C -- NULL
SUN_Emp_F -- NULL
SUN_Emp_P -- NULL


Now above data record I want to save it to tblWLEmpType_WLDay In following way...


tblWLEmpType_WLDay structure
CustLifeNo WLActCatId WLDayId WeekNo WLEmpTypeId
330374 FRI 1
330374 FRI 1 C
330374 M FRI 1
330374 M FRI 1
330374 M FRI 1 C
330374 FRI 1
330374 FRI 1
330374 FRI 1 C
330374 FRI 1
330374 D MON 1
330374 M MON 1
330374 M MON 1
330374 D MON 1 C
330374 MON 1
330374 MON 1 C
330374 MON 1
330374 M MON 1 C
330374 D MON 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SAT 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 SUN 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 THU 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 TUE 1
330374 M WED 1
330374 O WED 1
330374 O WED 1 C
330374 O WED 1
330374 WED 1
330374 WED 1 C
330374 WED 1
330374 M WED 1
330374 M WED 1 C


So what's the best sql to achive this task...


To Achive this I have allredy write sql Query but it's taking so long...
Here is the query in command save click event

Private Sub Command2_Click()
Dim x As Integer
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str4 As String
Dim d As String
Dim rsSQL As New ADODB.Recordset

rs.Open "SELECT distinct * FROM WLMod_Denormalized", cnn, adOpenKeyset, adLockReadOnly
rs.MoveFirst
x = rs.RecordCount

'Pick Up First Record From Denormalized table and update WLEmpType_WLDay
For x = 1 To x

If rs.Fields(8).Value = "C" Then ' Pick up the value from MON_EMP_C field and Save it to tblWLEmpType_WLDay

str1 = " UPDATE tblWLEmpType_WLDay INNER JOIN WLMod_Denormalized ON (tblWLEmpType_WLDay.WeekNo = WLMod_Denormalized.WeekNo)" & _
"AND (WLMod_Denormalized.CustLifeNo = tblWLEmpType_WLDay.CustLifeNo)" & _
" SET tblWLEmpType_WLDay.WLEmpTypeId = 'C'" & _
" WHERE [tblWLEmpType_WLDay]![WLDayId]='MON' " & _
" AND tblWLEmpType_WLDay.CustLifeNo = WLMod_Denormalized.CustLifeNo AND tblWLEmpType_WLDay.WeekNo=WLMod_Denormalized.WeekNo"

Set rsSQL = cnn.Execute(str1)

End If

If rs.Fields(9).Value = "F" Then ' Pick up the value from MON_EMP_F field and Save it to tblWLEmpType_WLDay

str1 = " UPDATE tblWLEmpType_WLDay INNER JOIN WLMod_Denormalized ON (tblWLEmpType_WLDay.WeekNo = WLMod_Denormalized.WeekNo)" & _
"AND (WLMod_Denormalized.CustLifeNo = tblWLEmpType_WLDay.CustLifeNo)" & _
" SET tblWLEmpType_WLDay.WLEmpTypeId = 'F'" & _
" WHERE [tblWLEmpType_WLDay]![WLDayId]='MON' " & _
" AND tblWLEmpType_WLDay.CustLifeNo = WLMod_Denormalized.CustLifeNo AND tblWLEmpType_WLDay.WeekNo=WLMod_Denormalized.WeekNo"

Set rsSQL = cnn.Execute(str1)

End If
If rs.Fields(10).Value = "P" Then ' Pick up the value from MON_EMP_P field and Save it to tblWLEmpType_WLDay

str1 = " UPDATE tblWLEmpType_WLDay INNER JOIN WLMod_Denormalized ON (tblWLEmpType_WLDay.WeekNo = WLMod_Denormalized.WeekNo)" & _
"AND (WLMod_Denormalized.CustLifeNo = tblWLEmpType_WLDay.CustLifeNo)" & _
" SET tblWLEmpType_WLDay.WLEmpTypeId = 'P'" & _
" WHERE [tblWLEmpType_WLDay]![WLDayId]='MON' " & _
" AND tblWLEmpType_WLDay.CustLifeNo = WLMod_Denormalized.CustLifeNo AND tblWLEmpType_WLDay.WeekNo=WLMod_Denormalized.WeekNo"

Set rsSQL = cnn.Execute(str1)


'**************
'continue same logic for each column....

'*******

End If
rs.MoveNext
Next x




End Sub




Please help me anyone...

Thanks
SQLDev...

 

[vongrunt]

SQL you provided does only UPDATE (Access syntax). Do you want to INSERT rows, only UPDATE or both (complete sync)?

VB code itself can be greatly reduced with some arrays/collections for mapping purposes.

FYI there is no need to save rows for which denormalized value is NULL (that's one of goals of 1NF).

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[sqldevrequiretektips]

Thanks for your reply...

I just want to do update no Insert....

All the custLifeNo and weekNo are even in WLDenormalized and tblWLEmpType_WLDay table...

So...could you give me update sql or logic...to achieve this task...

Thanks
SQLDEv

 

[tc3596]

As far as speed goes, try putting all this code into a stored proc. You are calling sql server for each record in your loop. By providing a proc, you lower the network traffic considerably and the query will be pre-compiled for faster execution. Secondly, do you have an index on your table being updated? Updates in sql server cause the table to be re-indexed every time it is updated. If an index does exist, you may want to try taking it off to see if it speeds things up.

In your statment:
"rs.Open "SELECT distinct * FROM WLMod_Denormalized", cnn, adOpenKeyset, adLockReadOnly"

Distinct doesn't help your speed and you also don't have a where clause in your statment. Do you need all records in the entire table?

Also, put some else if's in place. You only want to check the value if the previous condition is not met (is that correct?)
If rs.Fields(8).Value = "C" then
"Update...."
Else If rs.Fields(9).Value = "F" then
"Update...."
Else ......
End If

Hope this helps.

 

[vongrunt]

Although this is slightly OT (Access query, lotsa VB code), I think it is worth detailed reply thanks to denormalization thing.

First, what's the purpose of this:

If rs.Fields(9).Value = "F" 
	' perform UPDATE
End If

If someone changes value to NULL or something other than "F", row in normalized table won't be changed and you'll get UPDATE anomaly. According to expected results from your original post, this IF thing is not necessary at all. Correct me if I'm wrong.

Second and most important: there are 6*7 = 42 columns to denormalize. If source table has 1,000 rows you'll need 42,000 UPDATE statements. Of course this is slow. Scrap that. Basically you need one UPDATE statement for each denormalized column. Total: 42 mass UPDATEs. Here is one statement:

UPDATE tblWLEmpType_WLDay
INNER JOIN WLMod_Denormalized 
	ON (tblWLEmpType_WLDay.WeekNo = WLMod_Denormalized.WeekNo)
   AND (WLMod_Denormalized.CustLifeNo = tblWLEmpType_WLDay.CustLifeNo)
SET tblWLEmpType_WLDay.WLEmpTypeId = WLMod_Denormalized.[b]MON_Act_M[/b]
WHERE [tblWLEmpType_WLDay]![WLDayId]=[b]'MON'[/b]

Code in bold must be generated with dynamic SQL. The rest remains unchanged. To reduce copy&paste, in VB you can use arrays, dictionaries or some similar collections. Here is general idea (with query from above):

' days in week
Dim aDays
aDays = Array("MON", "TUE", "WED", "THU", "FRI", "SAT", "SUN")

' columns suffixes each day
Dim aSuffix
aSuffix = Array("Act_M", "Act_O", "Act_D", "Emp_C", "Emp_F", "Emp_P")

Dim sDay, sSuffix, sDenormalizedColumn
Dim str1

For Each sDay in aDays
    For Each sSuffix in aSuffix
		sDenormalizedColumn = sDay & "_" & sSuffix
		
		str1 = "UPDATE tblWLEmpType_WLDay " & _
			"INNER JOIN WLMod_Denormalized " & _ 
			"	ON (tblWLEmpType_WLDay.WeekNo = WLMod_Denormalized.WeekNo) " & _
   		"	AND (WLMod_Denormalized.CustLifeNo = tblWLEmpType_WLDay.CustLifeNo) " & _
			"SET tblWLEmpType_WLDay.WLEmpTypeId = WLMod_Denormalized." [b]& sDenormalizedColumn &[/b] " " & _
			"WHERE [tblWLEmpType_WLDay]![WLDayId]='" [b]& sDay &[/b] "'"
      		
      Call cnn.Execute(str1)		
    Next
Next

Stored procedure won't make this code significantly faster, though index on JOINed columns (WeekNo, CustLifeNo) is welcomed.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[sqldevrequiretektips]

Thanks for your reply...vongrunt
and also thanks to tc3596..

TC3596 :- I am using MsAccess on the backend side..so i can't really use store procedure concept...


Vongrunt...i think your code really helps for me...

I will try to implement and i will let you know how i did...


Thanks
SQLDev