You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
Version 1
Next »
Adding bulk UDF columns to an existing loan, party or deal.
First prepare the UDF table containing the UDF's to append to the target table. Below script creates that table based on an existing table, using the correct UDF datatypes.
-- table with udf fields to aad to existing table
declare @sourceTable varchar(100) = 'joined_202004';
with udfs as (
select
fieldname = replace(name,' ','_')
,prefixed = case when system_type_id = 231 or system_type_id = 167 then 'uc' + replace(name,' ','_')
when system_type_id = 56 then 'ui' + replace(name,' ','_')
when system_type_id = 104 then 'ub' + replace(name,' ','_')
when system_type_id = 106 or system_type_id = 62 then 'ud' + replace(name,' ','_')
when system_type_id = 61 then 'ut' + replace(name,' ','_')
end
, udfDataType = case when system_type_id = 231 or system_type_id = 167 then 0
when system_type_id = 56 then 1
when system_type_id = 104 then 4
when system_type_id = 106 or system_type_id = 62 then 2
when system_type_id = 231 or system_type_id = 61 then 3
end
, sqlDataType = case when system_type_id = 231 or system_type_id = 167 then 'varchar(' + cast(max_length as varchar) + ')'
when system_type_id = 56 then 'int'
when system_type_id = 104 then 'bit'
when system_type_id = 106 or system_type_id = 62 then 'decimal(18,' + cast([scale] as varchar) + ')'
when system_type_id = 61 then 'datetime'
end
,system_type_id
,max_length
,scale
from sys.columns
where object_id = (select object_id from sys.tables where name = @sourceTable)
)
-- table holding udf data to append to table
select *
into udf_columns
from udfs
Next, append the UDF fields to the loan/, party or deal:
declare @targetTable varchar(100) = 'loans2'
declare @alter table (idx int, alterText nvarchar(1000));
with udfs as (select * from udf_columns)
INSERT INTO @alter (idx,alterText)
select
idx = ROW_NUMBER() over (order by prefixed)
,alterText = 'alter table ' + @targetTable + ' add ' + prefixed + ' ' + sqlDataType
from udfs;
declare @counter int = 1
declare @maxRow int = (select count(*) from @alter)
declare @alterText nvarchar(1000) = ''
while @counter <= @maxRow
begin
select @alterText=alterText from @alter where idx=@counter
-- print @alterText
exec sp_executesql @alterText
set @counter = @counter + 1
end