Thank you for the help.

Currently I have this:

Still getting an error which has now become:

(12100 row(s) affected)

Msg 102, Level 15, State 1, Line 27

Incorrect syntax near '('.

Trying to find what the issue is, Sorry if i'm way off in how i should be going about this.

Many thanks

R

set @SQL = N'SELECT

SUBSTRING(BulkColumn, 1, 4) AS patid

,SUBSTRING(BulkColumn, 5, 1) AS patflag

,CASE

WHEN ISDATE(SUBSTRING(BulkColumn, 6, 8)) = 1

THEN CONVERT(datetime,SUBSTRING(BulkColumn, 31, 8)) ELSE NULL

END AS yob

,SUBSTRING(BulkColumn, 14, 6) AS famnum

,SUBSTRING(BulkColumn, 20, 1) AS sex

,CONVERT(datetime,SUBSTRING(BulkColumn, 21, 8)) AS regdate

,SUBSTRING(BulkColumn, 29, 2) AS regstat

,CASE

WHEN ISDATE(SUBSTRING(BulkColumn, 31, 8)) = 1

THEN CONVERT(datetime,SUBSTRING(BulkColumn, 31, 8)) ELSE NULL

END AS xferdate

,SUBSTRING(BulkColumn, 39, 2) AS regrea

,CASE

WHEN ISDATE(SUBSTRING(BulkColumn, 41, 8)) = 1

THEN CONVERT(datetime,SUBSTRING(BulkColumn, 41, 8)) ELSE NULL

END AS deathdate

,SUBSTRING(BulkColumn, 49, 1) AS deathinfo

,SUBSTRING(BulkColumn, 50, 1) AS accept

,SUBSTRING(BulkColumn, 51, 1) AS institute

,SUBSTRING(BulkColumn, 52, 2) AS marital

,SUBSTRING(BulkColumn, 54, 1) AS dispensing

,SUBSTRING(BulkColumn, 55, 2) AS prscexempt

,CONVERT(datetime,SUBSTRING(BulkColumn, 57, 8)) AS sysdate

into ' + @tblname + ' FROM #stage'

exec TestDB..sp_executesql @SQL