quote:

---

H|20120531|SAPECC6|
D|CostCentreID|CostCentreName|CostCentreDescription|
D|700|Name1|Description 1|
D|701|Name2|
D|702|Name3|Description 3|
D|703|Name4|
T|4|

---

CostCentreID  CostCentreName   CostCentreDescription  SourceSystem ExtractDate
700               Name1                Description1   SAPECC6       2012-05-31
701               Name2                               SAPECC6       2012-05-31
702               Name3                Description3   SAPECC6       2012-05-31
703               Name4                               SAPECC6       2012-05-31

create table dbo.rka(CostCentreID int, CostCentreName varchar(50), CostCentreDescription varchar(50), SourceSystem varchar(50), ExtractDate datetime)

ingest to a staging table named CostCenter

insert into dbo.rka(CostCentreID , CostCentreName, CostCentreDescription , SourceSystem, ExtractDate )
SELECT cs1.[COLUMN 1], cs1.[COLUMN 2], cs1.[COLUMN 3], a.[COLUMN 2], CAST(a.[COLUMN 1] AS DATETIME)
  FROM [dbo].[CostCenter] cs1
  cross apply (SELECT [COLUMN 1], [COLUMN 2] FROM [dbo].[CostCenter] cs1 where [COLUMN 0] = N'H') a
  where ISNUMERIC(cs1.[COLUMN 1]) = 1
  and cs1.[COLUMN 0] = 'D'