incremental load process

• craig 84462

  Say Hey Kid

  Points: 669

  More actions

  March 26, 2010 at 2:32 pm

  #231256

  Hi there,

  I have prepared a staging table and am trying to 'populate' a transform table with the same data BUT at the same time allow an incremental extraction process from the staging table such that the additional record with address with 'gifford rd' comes into the transform table 1 day later. At the moment it is not inserting, attached is my code below , please let me know if there is anything wrong with it or something needed changing.

  ************************************

  use Craig

  go

  --Set up a staging table

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[craigteststaging]') AND type in (N'U'))

  DROP TABLE [dbo].[craigteststaging]

  GO

  create table craigteststaging

  (

  callid int not null,

  address varchar (30) not null

  )

  --set up the transform table

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[craigtestdestinationtransform]') AND type in (N'U'))

  DROP TABLE [dbo].[craigtestdestinationtransform]

  GO

  create table craigtestdestinationtransform

  (callid int not null,

  address varchar (30) not null,

  DateFrom DateTime Not Null,

  DateTo DateTime Not Null

  )

  declare @updatedate datetime

  set @updatedate= '2010-03-30'

  -- records provided for staging table

  insert craigteststaging (callid,address

  )

  values

  (1,'33 cliff rd' ),

  (2,'36 venue st' ),

  (4,'67 parnell rd' )

  --populate the transform table

  insert craigtestdestinationtransform (callid,[address],DateFrom,dateto

  )

  select callid ,

  [address],

  '2010-03-29' as datefrom ,

  '2099-12-31' as dateto

  from craigteststaging

  --on the day after today (30 march) we change 1 address with call id '4'

  insert craigteststaging (callid,[address]

  )

  values

  (4,'988 gifford rd' )

  --update date to to 30 march,2009 as want to get this record into transform table

  update craigtestdestinationtransform

  set DateTo= @updatedate

  from craigtestdestinationtransform t

  left join

  craigteststaging d on

  t.callid=d.callid

  and t.DateTo= '2099-12-31'

  where d.[address] is null

  --insert the new row into transform table

  insert craigtestdestinationtransform (callid,[address],DateFrom,dateto )

  select z.callid,

  z.[address],

  @updatedate as datefrom,

  '2099-12-31' as dateto

  from

  craigtestdestinationtransform z

  right join craigteststaging d

  on z.callid=d. callid

  and z.DateTo= '2099-12-31'

  where z.[address] is null