/****Child Table ****/

/****** Object: Table [dbo].[ChildDetails] Script Date: 01/29/2014 08:21:30 ******/

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

DROP TABLE [dbo].[ChildDetails]

GO

/****** Object: Table [dbo].[ChildDetails] Script Date: 01/29/2014 08:21:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ChildDetails](

[PER_ID] [numeric](8, 0) NULL,

[NAM_TITLE] [varchar](10) NULL,

[NAM_FORE1] [varchar](35) NULL,

[NAM_SURNAME] [varchar](35) NULL,

[PER_DOB] [datetime] NULL,

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****Referral Table ****/

/****** Object: Table [dbo].[ChildReferrals] Script Date: 01/29/2014 08:22:27 ******/

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

DROP TABLE [dbo].[ChildReferrals]

GO

/****** Object: Table [dbo].[ChildReferrals] Script Date: 01/29/2014 08:22:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ChildReferrals](

[CLI_PER_ID] [numeric](8, 0) NULL,

[REF_ID] [numeric](8, 0) NULL,

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****Referral Action Table ****/

/****** Object: Table [dbo].[ChildReferralAction] Script Date: 01/29/2014 08:22:51 ******/

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

DROP TABLE [dbo].[ChildReferralAction]

GO

/****** Object: Table [dbo].[ChildReferralAction] Script Date: 01/29/2014 08:22:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ChildReferralAction](

[CLI_PER_ID] [numeric](8, 0) NULL,

[RAC_ID] [numeric](8, 0) NULL,

[RAC_REF_ID] [numeric](8, 0) NULL,

[RAC_TYPE] [varchar](10) NULL,

[RAC_DATE] [datetime2](7) NULL,

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****Populate Referral Action Table ****/

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT ChildReferrals ON

--===== Insert the test data into the test table

INSERT INTO ChildReferrals

(CLI_PER_ID,REF_ID)

SELECT '27429','619' UNION ALL

SELECT '27429','620' UNION ALL

SELECT '27429','621' UNION ALL

SELECT '27429','622' UNION ALL

SELECT '27429','624' UNION ALL

SELECT '27429','860729'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT ChildReferrals OFF

/****Populate Child Table ****/

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT ChildDetails ON

--===== Insert the test data into the test table

INSERT INTO ChildDetails

(PER_ID,NAM_TITLE,NAM_FORE1,NAM_SURNAME,PER_DOB)

SELECT '27429','MR','ROBERT','OAKES','Jul 6 1956 12:00AM'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT ChildDetails OFF

/****Populate Referral Action ****/

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT ChildReferralAction ON

--===== Insert the test data into the test table

INSERT INTO ChildReferralAction

(CLI_PER_ID,RAC_ID,RAC_REF_ID,RAC_TYPE,RAC_DATE)

SELECT '27429','719','619','ALLOCATE ','1990-03-22 00:00:00.0000000' UNION ALL

SELECT '27429','720','619','CLOS ','1999-06-03 00:00:00.0000000' UNION ALL

SELECT '27429','721','620','ALLOCATE ','1988-07-04 00:00:00.0000000' UNION ALL

SELECT '27429','722','620','CLOS ','1990-03-22 00:00:00.0000000' UNION ALL

SELECT '27429','723','621','ALLOCATE ','1991-11-14 00:00:00.0000000' UNION ALL

SELECT '27429','724','621','CLOS ','1999-04-14 00:00:00.0000000' UNION ALL

SELECT '27429','725','622','ALLOCATE ','1999-04-14 00:00:00.0000000' UNION ALL

SELECT '27429','726','622','CLOS ','2000-10-10 00:00:00.0000000' UNION ALL

SELECT '27429','729','624','ALLOCATE ','2001-04-25 00:00:00.0000000' UNION ALL

SELECT '27429','730','624','CLOS ','2002-06-17 00:00:00.0000000' UNION ALL

SELECT '27429','1392816','860729','REQT ','2009-01-12 00:00:00.0000000' UNION ALL

SELECT '27429','1394845','860729','ACCT ','2009-01-12 00:00:00.0000000' UNION ALL

SELECT '27429','1397310','860729','ALLOCATE ','2009-01-13 00:00:00.0000000' UNION ALL

SELECT '27429','1443476','860729','ALLOCATE ','2009-02-23 00:00:00.0000000' UNION ALL

SELECT '27429','2121498','860729','ALLOCATE ','2011-01-31 00:00:00.0000000' UNION ALL

SELECT '27429','2688764','860729','CLOS ','2013-08-16 00:00:00.0000000'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT ChildReferralAction OFF

Fully tested... Again sorry 🙂