March 27, 2010 at 1:17 am
Hey, has anyone does this before??? Just would like to see some code about how this is done.
like for eg: if a new row is changed or inserted into a staging table in a warehouse how can we use dates to insert this into the transform table??? This is getting the deltas from the staging table
some code I have written is here:
******************************
use Craig
go
declare @updatedate datetime
set @updatedate= '2010-03-30'
update craigtestdestinationtransform
set DateTo= @updatedate
from craigtestdestinationtransform t
left join
craigteststaging d on
t.callid=d.callid
and t.[source]=d.[source]
and t.DateTo= '2099-12-31'
where t.callid is null
--insert the new row into transform table
insert craigtestdestinationtransform
select d.callid,
d.[address],
@updatedate as datefrom,
'2099-12-31' as dateto,
d.[source]
from
craigtestdestinationtransform z
right join craigteststaging d
on z.callid=d.callid
and d.[source]=z.[source]
and z.DateTo= '2099-12-31'
where z.callid is null
March 27, 2010 at 11:39 am
Not sure why you would need to be testing a date if the staging table already contains the rows that you need? And I certainly do not know why you would be testing the dates of the destination table, instead of the source table or staging table??
Finally, it is almost certainly incorrect to test for NULL in the outer table's join column of an outer join. Testing for it in the inner table might makes sense (in order to emulate an anti-semi-join), but testing for it in the Outer table should normally result in no rows ever being returned.
If you could provide an example or two, it might help us to understand better what you are trying to do.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2010 at 4:27 pm
I am trying to understand what the update does in the below code and trying to understand by doing that other example. In this example I do not understand how the transform call port table gets populated in the first instance, thats the biggest issue at the moment
******************************************************
load_Transform_CallPort.sql
Populate Transform.dbo.CallPort table from various sources of CallPort values.
Notes:
1.
Transformations performed:
1. Calculate:
- CallPortName- First part of CallPort, e.g. INB, T2K
- CallPortNumber- Second part of CallPort
Parameters:
1. None
Inputs:
1. IKE.Staging[_AU/_NZ/_UK/_ZA].ptActivityDB.dbo.Call
2. ...
Outputs:
1. IKE.Transform.dbo.CallPort
Mods:
2010-03-25, ASC, First written.
*/
UseCraig
Go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.load_Transform_CallPort') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.load_Transform_CallPort
Go
CreateProcedureload_Transform_CallPort
As
Begin
SetNoCountOn
Declare@UpdateDateDateTime
Declare@ProcessLogIdInt
Declare@CountOfRowsInt
-- Log start of load...
ExecMetadata.dbo.uspControlProcessLog_Start
'load_Transform_CallPort'
,@ProcessLogIdOutput
Set@UpdateDate=GetDate()
CreateTable#CallPort
(CallPortnvarChar( 20)
,[Source]varChar(100)
)
truncate table #callport
-- Pull in CallPort values from various domains of use
Begin Try
Insert#CallPort
SelectDistinct
CallPortAsCallPort
,'Staging_AU_ptActivityDB_Call'As[Source]
FromStaging_AU.dbo.ptActivityDB_Call
WhereCallPortIs Not Null
End Try
Begin Catch
ExecMetadata.dbo.usp_GetErrorInfo
End Catch
-- Update Transform table with new values
Begin Try
-- Close off records no longer seen...
UpdateTransform.dbo.CallPort
SetDateTo=@UpdateDate
FromTransform.dbo.CallPortm
Left Join
#CallPortt
Onm.CallPort=t.CallPort
Andm.[Source]=t.[Source]
Wheret.CallPortIs Null-- Row no longer in new table
Andm.DateTo='2099-12-31'-- Only comparing against the latest record
Set@CountOfRows=@@ROWCOUNT
-- ...and add new values.
InsertTransform.dbo.CallPort
(CallPort
,CallPortName
,CallPortNumber
,[Source]
,DateFrom
,DateTo
)
Select
t.CallPort
,CaseWhenCHARINDEX('.',t.CallPort)>0
ThenSUBSTRING(t.CallPort
,1
,CHARINDEX('.',t.CallPort)-1
)
Else''
EndAsCallPortName
,CaseWhenCHARINDEX('.',t.CallPort)>0And
CHARINDEX('.',t.CallPort)<DATALENGTH(t.CallPort)-1
ThenSUBSTRING(t.CallPort
,CHARINDEX('.',t.CallPort)+1
,DATALENGTH(t.CallPort)-CHARINDEX('.',t.CallPort)
)
Else''
EndAsCallPortNumber
,t.[Source]
,@UpdateDateAsDateFrom
,'2099-12-31'AsDateTo
FromTransform.dbo.CallPortm
Right Join
#CallPortt
Onm.CallPort=t.CallPort
Andm.[Source]=t.[Source]
Andm.DateTo='2099-12-31'-- Only comparing against the latest record
Wherem.CallPortIs Null-- Row not in old table
Set@CountOfRows=@CountOfRows + @@ROWCOUNT
End Try
Begin Catch
ExecMetadata.dbo.usp_GetErrorInfo
End Catch
Set@CountOfRows=@@ROWCOUNT
-- Log end of load
ExecMetadata.dbo.uspControlProcessLog_End
@ProcessLogId
,@CountOfRows
End
Go
-- Test exec
Execdbo.load_Transform_CallPort
March 27, 2010 at 5:16 pm
If you could, please use the code tags, which make our lives much easier, as below:
/******************************************************
load_Transform_CallPort.sql
Populate Transform.dbo.CallPort table from various sources of CallPort values.
Notes:
1.
Transformations performed:
1. Calculate:
- CallPortName- First part of CallPort, e.g. INB, T2K
- CallPortNumber- Second part of CallPort
Parameters:
1. None
Inputs:
1. IKE.Staging[_AU/_NZ/_UK/_ZA].ptActivityDB.dbo.Call
2. ...
Outputs:
1. IKE.Transform.dbo.CallPort
Mods:
2010-03-25, ASC, First written.
*/
UseCraig
Go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.load_Transform_CallPort') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.load_Transform_CallPort
Go
CreateProcedureload_Transform_CallPort
As
Begin
SetNoCountOn
Declare@UpdateDateDateTime
Declare@ProcessLogIdInt
Declare@CountOfRowsInt
-- Log start of load...
ExecMetadata.dbo.uspControlProcessLog_Start
'load_Transform_CallPort'
,@ProcessLogIdOutput
Set@UpdateDate=GetDate()
CreateTable#CallPort
(CallPortnvarChar( 20)
,[Source]varChar(100)
)
truncate table #callport
-- Pull in CallPort values from various domains of use
Begin Try
Insert#CallPort
SelectDistinct
CallPortAsCallPort
,'Staging_AU_ptActivityDB_Call'As[Source]
FromStaging_AU.dbo.ptActivityDB_Call
WhereCallPortIs Not Null
End Try
Begin Catch
ExecMetadata.dbo.usp_GetErrorInfo
End Catch
-- Update Transform table with new values
Begin Try
-- Close off records no longer seen...
UpdateTransform.dbo.CallPort
SetDateTo=@UpdateDate
FromTransform.dbo.CallPortm
Left Join
#CallPortt
Onm.CallPort=t.CallPort
Andm.[Source]=t.[Source]
Wheret.CallPortIs Null-- Row no longer in new table
Andm.DateTo='2099-12-31'-- Only comparing against the latest record
Set@CountOfRows=@@ROWCOUNT
-- ...and add new values.
InsertTransform.dbo.CallPort
(CallPort
,CallPortName
,CallPortNumber
,[Source]
,DateFrom
,DateTo
)
Select
t.CallPort
,CaseWhenCHARINDEX('.',t.CallPort)>0
ThenSUBSTRING(t.CallPort
,1
,CHARINDEX('.',t.CallPort)-1
)
Else''
EndAsCallPortName
,CaseWhenCHARINDEX('.',t.CallPort)>0And
CHARINDEX('.',t.CallPort)<DATALENGTH(t.CallPort)-1
ThenSUBSTRING(t.CallPort
,CHARINDEX('.',t.CallPort)+1
,DATALENGTH(t.CallPort)-CHARINDEX('.',t.CallPort)
)
Else''
EndAsCallPortNumber
,t.[Source]
,@UpdateDateAsDateFrom
,'2099-12-31'AsDateTo
FromTransform.dbo.CallPortm
Right Join
#CallPortt
Onm.CallPort=t.CallPort
Andm.[Source]=t.[Source]
Andm.DateTo='2099-12-31'-- Only comparing against the latest record
Wherem.CallPortIs Null-- Row not in old table
Set@CountOfRows=@CountOfRows + @@ROWCOUNT
End Try
Begin Catch
ExecMetadata.dbo.usp_GetErrorInfo
End Catch
Set@CountOfRows=@@ROWCOUNT
-- Log end of load
ExecMetadata.dbo.uspControlProcessLog_End
@ProcessLogId
,@CountOfRows
End
Go
-- Test exec
Execdbo.load_Transform_CallPort
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply