Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using PL SQL - How to Insert Records 1

Status
Not open for further replies.
sqldevrequiretektips

sqldevrequiretektips

Programmer
Dec 28, 2004
87
US
Table Name : WLCustAct_Emp
Structure and data

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

Table WLCust_Denormalized :

CustLifeNo,
WeekNo,
MON_Act_M,
MON_Act_O
MON_Act_D
MON_Emp_C
MON_Emp_F
MON_Emp_P

TUE_Act_M
TUE_Act_O
TUE_Act_D
TUE_Emp_C
TUE_Emp_F
TUE_Emp_P

WED_Act_M
WED_Act_O
WED_Act_D
WED_Emp_C
WED_Emp_F
WED_Emp_P

THU_Act_M
THU_Act_O
THU_Act_D
THU_Emp_C
THU_Emp_F
THU_Emp_P

FRI_Act_M
FRI_Act_O
FRI_Act_D
FRI_Emp_C
FRI_Emp_F
FRI_Emp_P

SAT_Act_M
SAT_Act_O
SAT_Act_D
SAT_Emp_C
SAT_Emp_F
SAT_Emp_P

SUN_Act_M
SUN_Act_O
SUN_Act_D
SUN_Emp_C
SUN_Emp_F
SUN_Emp_P

I want to insert recrods from In tblWLCust_Denormalized table from WLCustAct_Emp table

More in detail M,O,D Value belogs to Act So, And C,F,P value Belongs to Emp

After Insert query Table Denormalized will have only one record..

Any help really appriciate...

Thx
AD
 
Sort by date Sort by votes
The most straight forward way to do that is to do it in a series of updates.
First insert the CustLifeNo and WeekNo leaving the other columns blank null (redefine the columns if necessary). You can use a Select Distinct or Group By to get CustLifeNo and WeekNo.
Then do updates by using an inner join between tables with the appropriate filters. You can put all of the steps into one SP so that when you are done you will have a simple SP that could be run to do it again, if necessary.
Need more help?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Please post in the correct forum. You are using PL/SQL and that is not a Microsoft SQL Server language (which is what this forum is for).

I suggest you would be better off posting in an ORACLE forum as Oracle DOES use PL/SQL.

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Thanks for your reply...

But Is anyone can help me to write SP or SQL query to achieve this task..

Let me know if you need anything from my side i reply you same day probably with in 30 minutes as you reply me...

I am really stuck on this and i have to achieve this task

I allready give you structure of table with data so please help me with Logic/ syntex....

Thx
AD
 
Upvote 0 Downvote
Also I have posted on oracle pL/sql form but nobody reply me....

Am i missing something? I hope you guys understand what i am trying to do... Please let me know if anyone need more information on my question..

Thx
AD
 
Upvote 0 Downvote
Code: 
[Blue]INSERT[/Blue] [Blue]INTO[/Blue] WLCust_Denormalized 
   [Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] CustLifeNo[Gray],[/Gray] WeekNo [Blue]FROM[/Blue] WLCustAct_Emp
[Blue]UPDATE[/Blue] WLCust_Denormalized 
   [Blue]SET[/Blue] MON_Emp_M[Gray]=[/Gray]WhatEver[Gray],[/Gray] OtherFields[Gray]=[/Gray]OtherAmts
   [Blue]FROM[/Blue] WLCust_Denormalized D [Blue]INNER[/Blue] [Gray]JOIN[/Gray] WLCustAct_Emp E 
      [Blue]ON[/Blue] D.CustLifeNo[Gray]=[/Gray]E.CustLifeNo [Gray]AND[/Gray] D.WeekNo[Gray]=[/Gray]E.WeekNo
   [Blue]WHERE[/Blue] WhatEverFilterNeeded
You will have to design WLCust_Denormalized to either accept NULLS or set default values. Add other Update statements for the other columns. Does this help? If not then you need to provide examples of before and after data.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Now I am getting an error Incorrect syntex near case

please help me on this...

F.Y.I. - > I had to change table names an field names as my
manager requested...

UPDATE Test_Denormalized
case when WLActEmp.WLActCatId = 'M' And WLActEmp.WLDayId = 'MON' Then
SET Test_Denormalized.MON_Act_M= 'M'
when WLActEmp.WLActCatId = 'O' And WLActEmp.WLDayId = 'MON'Then
Set Test_Denormalized.MON_Act_O = 'O'
when WLActEmp.WLActCatId = 'D' And WLActEmp.WLDayId = 'MON' Then
Set Test_Denormalized.MON_Act_D = 'D'
End
FROM Test_Denormalized D INNER JOIN WLActEmp E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
 
Upvote 0 Downvote
You can't use the Case statement that way. You have to make those separate Update statements. I have to run to the airport, but give it another shot and I'll help again when I get back.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Thanks for your concern...donutman..

Please help me out when you get a chance..

From my side i will keep trying...

Thx
AD
 
Upvote 0 Downvote
Can you show a row of the denormalized data and the table rows from which the data comes from.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
denormalized table - New table which doesn't have any records...

but let say if i insert manually above record (table Name :WLCustAct_Emp - CustLifeNo=330374 ) to denormalized table...It should look like this...

Table Name : WLCustAct_Emp
Structure and data

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

In if i insert above record manually to WLCust_Denormalized table :- see below..


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


If you still have any question then i can forward you sample access database file..

Let me know if you have any questions..

Thank you much for your concern..

AD
 
Upvote 0 Downvote
Code: 
[Blue]INSERT[/Blue] [Blue]INTO[/Blue] WLCust_Denormalized 
   [Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] CustLifeNo[Gray],[/Gray] WeekNo [Blue]FROM[/Blue] WLCustAct_Emp
[Blue]UPDATE[/Blue] WLCust_Denormalized 
   [Blue]SET[/Blue] MON_Act_M[Gray]=[/Gray][red]'M'[/red]
   [Blue]FROM[/Blue] WLCust_Denormalized D [Blue]INNER[/Blue] [Gray]JOIN[/Gray] WLCustAct_Emp E 
      [Blue]ON[/Blue] D.CustLifeNo[Gray]=[/Gray]E.CustLifeNo [Gray]AND[/Gray] D.WeekNo[Gray]=[/Gray]E.WeekNo
   [Blue]WHERE[/Blue] WLDayID[Gray]=[/Gray][red]'MON'[/red] [Gray]AND[/Gray] WLActCatId[Gray]=[/Gray][red]'M'[/red]
[Blue]UPDATE[/Blue] WLCust_Denormalized 
   [Blue]SET[/Blue] MON_Act_O[Gray]=[/Gray][red]'O'[/red]
   [Blue]FROM[/Blue] WLCust_Denormalized D [Blue]INNER[/Blue] [Gray]JOIN[/Gray] WLCustAct_Emp E 
      [Blue]ON[/Blue] D.CustLifeNo[Gray]=[/Gray]E.CustLifeNo [Gray]AND[/Gray] D.WeekNo[Gray]=[/Gray]E.WeekNo
   [Blue]WHERE[/Blue] WLDayID[Gray]=[/Gray][red]'MON'[/red] [Gray]AND[/Gray] WLActCatId[Gray]=[/Gray][red]'O'[/red]
[Blue]UPDATE[/Blue] WLCust_Denormalized
   [Blue]SET[/Blue] MON_Act_D[Gray]=[/Gray][red]'D'[/red]
   [Blue]FROM[/Blue] WLCust_Denormalized D [Blue]INNER[/Blue] [Gray]JOIN[/Gray] WLCustAct_Emp E 
      [Blue]ON[/Blue] D.CustLifeNo[Gray]=[/Gray]E.CustLifeNo [Gray]AND[/Gray] D.WeekNo[Gray]=[/Gray]E.WeekNo
   [Blue]WHERE[/Blue] WLDayID[Gray]=[/Gray][red]'MON'[/red] [Gray]AND[/Gray] WLActCatId[Gray]=[/Gray][red]'D'[/red]
You will have to add additional Updates to handle the other columns. It takes 1 Update statement for each column!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
I am going to complete whole query and i will let you know today...

Thanks...Thanks....Thanks....
AD
 
Upvote 0 Downvote
Thank you so much...It's working fine..

It's taking little bit long...so if that's the only way we have to do then it's ok...

if you have more suggesion to imporve perfomance..let me know..

In my case I am executing this query on command click event of VB...

In short when user dowload some data then some point when user go on another form there i am inserting this records in denormalized table..


Thanks
AD
 
Upvote 0 Downvote
Hello Donutman

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

If you remember me you help me to write sql to insert data from Denormalized table to Normalized table...


Now I in my project i have to go reverse...Another words...
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...

Help me when u get a chance...

Thanks
SQLDev...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Question
Parameterised sql server query
Replies
8
Views
407
Chriss Miller
C
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
553
Chris Miller
Chris Miller
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
591
Chris Miller
Chris Miller
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
768
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
644
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top