DECLARE @SQL VARCHAR(MAX) = 'char(1) sit_cass_flag
datetime OrderDate
varchar(10) OrderNumber
char(1) sit_dpv_confirm
char(1) sit_dpv_cmra
char(1) sit_dpv_vacant
char(2) sit_predir
char(2) sit_ln_trav_ind
char(2) sit_dpv_footnote1
char(2) sit_dpv_footnote2
char(2) sit_lacslink_ind
char(2) sit_geo_match_cd_sds
char(4) PropertyState
char(4) sit_sfx
char(4) sit_st
char(4) sit_match
char(4) sit_pfx
char(4) sit_err_code
char(4) sit_ln_trav
char(4) sit_geo_match_cd
char(4) sit_high_unit
datetime CompletedDate
char(5) sit_cr_rt
varchar(10) rowid
varchar(10) sit_postdir
varchar(10) source_name
varchar(10) sit_nbr
varchar(10) PropertyZip
varchar(10) fips
varchar(10) sit_zip4
varchar(10) st_nbr_to
varchar(10) sit_fract
varchar(10) sit_unit
varchar(20) EstimatedValue
varchar(20) AppraisedValue
varchar(20) sit_zip5
varchar(30) PropertyStreetNumber
varchar(30) InspectionDate
varchar(30) LoanPurpose
varchar(30) PropertyCounty
varchar(30) PropertyType
varchar(30) sit_str
varchar(30) sit_city
char(2) sitdpdc
varchar(40) PropertyCity
varchar(40) PropertyUnitNumber
char(4) sit_vaity_city
varchar(40) AppraiserLastName
varchar(6) sit_cens_tr
char(1) sit_cens_blk
varchar(6) sit_cens_blk2
char(1) sit_cens_blk_sfx
varchar(40) AppraiserFirstName
char(1) sit_unit_type
varchar(50) LoanNumber
char(1) sit_low_unit
varchar(50) PropertyStreetName
varchar(50) BorrowerFirstName
varchar(50) BorrowerLastName
varchar(100) ProductName
varchar(100) sit_addr
varchar(100) OriginalAddress
varchar(100) BorrowerName
varchar(32) FRST_NM1
varchar(12) MI_NM1
varchar(30) LST_NM1
varchar(4) SFX_ID1
varchar(32) FRST_NM2
varchar(12) MI_NM2
varchar(30) LST_NM2
varchar(4) SFX_ID2
'
SELECT 'CREATE TABLE FNMA_2(
' + Util.dbo.StringConcat(C2 + ' ' + C1 + ' NULL', ',
') + '
)'
FROM Util.dbo.ParseDelimitedTableColumns32(@sql, ' ', '
')
-- INSERT SCRIPT
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'INSERT fnma_2 with (TABLOCK)(' + Util.dbo.StringConcat(co.name, ', ') + ')
SELECT
' + Util.dbo.StringConcat(CASE WHEN ts.name LIKE '%date%'
THEN 'Util.dbo.TryParseDateTime(' + co.name + ') as ' + co.name
ELSE 'CASE WHEN LTRIM(' + co.name
+ ') IN ('''', ''NULL'') THEN NULL ELSE LTRIM(' + co.name + ') END as '
+ co.name
END, ',
') + '
FROM FNMA (NOLOCK)'
FROM sys.columns AS co (NOLOCK)
INNER JOIN sys.types AS ts (NOLOCK) ON ts.user_type_id = co.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(ts.name, co.max_length, co.PRECISION, co.scale, co.collation_name,
db.collation_name) ct
WHERE co.OBJECT_ID = OBJECT_ID('fnma_2')
EXEC Util.dbo.PrintLargeText
@SQL
-- CREATE PRIMARY KEY
-- ALTER TABLE fnma_2 ADD CONSTRAINT FNMA_PKC PRIMARY KEY CLUSTERED (OrderNumber) WITH (DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON)