Suggestions on processing result set in T-SQL
-
Attempting to get column name from varchar staging table and data type from target table.
Query works, but need ideas on processing rows returned to select IsDate, IsNumeric .. based on data type.
select is1.column_name, is2.data_type
from information_schema.columns is1,
information_schema.columns is2
where is1.table_name = 'StagingIn
and is2.table_name = 'Target'
and is1.column_name = is2.column_name
and is2.data_type <> 'varchar'
and is2.data_type <> 'char'
order by is2.data_type, is1.column_name
This returns all columns which are not varchar or char. Need to look at data type then programatically create something like
Update StagingIn
Set PromiseDate = '01/01/1900'
Where IsDate(PromiseDate) = 0
obviously replacing column name and test type as required to do all date and numeric types.
Thanks
-
Hi , if I understand, may be this code help you!
select 'update ' + object_name(id)
+ ' set [' + c.name + '] = '
+ case c.xtype when 61 then '''1900-01-01'''
when 52 then '0'
else 'null' end
+ case c.xtype when 61 then ' where isdate([' + c.name + ']) = 0'
when 52 then ' where isnumeric([' + c.name + ']) = 0'
else 'null' end
from syscolumns c , spt_values t
where c.id = object_id('sysdatabases')
and t.name in ('datetime','smallint')
and t.type = 'J'
and c.xtype = t.low
The 'sysdatabases' is only an example, put your own table name in there, please let me know if work for you.
:.::.:.::
-
Thank you very much for the push.
I modified slightly to get the xtype from the 2nd table and added all data types which concerned me.
Had to add master.. reference for spt_values. Seems to yield exactly what I need.
Does the CASE statement support multiple test values on a single row (CASE ... when (58, 61, 111) to avoid the dup lines? Couldn't find a delimeter which worked.
Final code
select 'UPDATE ' + object_name(c1.id)
+ ' SET [' + c1.name + '] = '
+ case c2.xtype when 58 then '''1900-01-01'''
when 61 then '''1900-01-01'''
when 111 then '''1900-01-01'''
when 62 then '0'
when 109 then '0'
when 59 then '0'
when 56 then '0'
when 38 then '0'
when 52 then '0'
when 48 then '0'
when 60 then '0'
when 110 then '0'
when 122 then '0'
else 'null' end
+ case c2.xtype when 58 then ' WHERE ISDATE([' + c1.name + ']) = 0'
when 61 then ' WHERE ISDATE([' + c1.name + ']) = 0'
when 111 then ' WHERE ISDATE([' + c1.name + ']) = 0'
when 62 then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
when 109 then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
when 59 then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
when 56 then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
when 38 then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
when 52 then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
when 48 then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
when 60 then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
when 110 then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
when 122 then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
else 'null'
end
from syscolumns c1, syscolumns c2, master..spt_values t
where c1.id = object_id('stagingin_cc')
and c2.id = object_id('prospect')
and c1.name = c2.name
and t.name in ('intn', 'tinyint', 'smallint', 'int', 'smalldatetime', 'real',
'money', 'datetime','float', 'floatn', 'moneyn', 'datetimn',
'smallmoney')
and t.type = 'J'
and c2.xtype = t.low
Yields:
UPDATE StagingIn_CC SET [PtpDate] = '1900-01-01' WHERE ISDATE([PtpDate]) = 0
UPDATE StagingIn_CC SET [Submit_Date] = '1900-01-01' WHERE ISDATE([Submit_Date]) = 0
UPDATE StagingIn_CC SET [CBR] = 0 WHERE ISNUMERIC([CBR]) = 0
UPDATE StagingIn_CC SET [DaysPd] = 0 WHERE ISNUMERIC([DaysPd]) = 0
UPDATE StagingIn_CC SET [DPassCnt] = 0 WHERE ISNUMERIC([DPassCnt]) = 0
UPDATE StagingIn_CC SET [LateChrgs] = 0 WHERE ISNUMERIC([LateChrgs]) = 0
UPDATE StagingIn_CC SET [NumPmtPd] = 0 WHERE ISNUMERIC([NumPmtPd]) = 0
UPDATE StagingIn_CC SET [OutstandingPersPropTaxDue] = 0 WHERE ISNUMERIC([OutstandingPersPropTaxDue]) = 0
UPDATE StagingIn_CC SET [PdOth1] = 0 WHERE ISNUMERIC([PdOth1]) = 0
UPDATE StagingIn_CC SET [PdOth2] = 0 WHERE ISNUMERIC([PdOth2]) = 0
UPDATE StagingIn_CC SET [PdOth3] = 0 WHERE ISNUMERIC([PdOth3]) = 0
UPDATE StagingIn_CC SET [PdOth4] = 0 WHERE ISNUMERIC([PdOth4]) = 0
UPDATE StagingIn_CC SET [PdOth5] = 0 WHERE ISNUMERIC([PdOth5]) = 0
UPDATE StagingIn_CC SET [PmtDD] = 0 WHERE ISNUMERIC([PmtDD]) = 0
UPDATE StagingIn_CC SET [TotalPastDueContracts] = 0 WHERE ISNUMERIC([TotalPastDueContracts]) = 0
UPDATE StagingIn_CC SET [Mktsource_syscode] = 0 WHERE ISNUMERIC([Mktsource_syscode]) = 0
UPDATE StagingIn_CC SET [AmtPd] = 0 WHERE ISNUMERIC([AmtPd]) = 0
-
Ok, Try with this...
select 'UPDATE ' + object_name(c1.id)
+ ' SET [' + c1.name + '] = '
+ case when c2.xtype in (58,61,111)
then '''1900-01-01'''
when c2.xtype in (62,109,59,56,38,52,48,60,110,122)
then '0'
else 'null'
end
+ case when c2.xtype in (58,61,111)
then ' WHERE ISDATE([' + c1.name + ']) = 0'
when c2.xtype in (62,109,59,56,38,52,48,60,110,122)
then ' WHERE ISNUMERIC([' + c1.name + ']) = 0'
else 'null'
end
from syscolumns c1, syscolumns c2, master..spt_values t
where c1.id = object_id('stagingin_cc')
and c2.id = object_id('prospect')
and c1.name = c2.name
and t.name in ('intn', 'tinyint', 'smallint', 'int', 'smalldatetime', 'real',
'money', 'datetime','float', 'floatn', 'moneyn', 'datetimn',
'smallmoney')
and t.type = 'J'
and c2.xtype = t.low
Note, that the syntax of case is "Case when boolean_express then value when boolean_express then value else value end" whithout a column name after "case".
And... excuse me about my english, its no good, how can you see
Another thing, 'dateTimn', 'floatn', 'intn' is a user data type? or just an error syntax? it seems like a user data type
:.::.:.::
-
Thanks again for the assistance. It looks like this will do exactly what I need and I can replace some hard coded stuff to make the package much more flexible.
On your question, I ran select * from master..spt_values where type = 'J' order by low and got the following result set.
COMPATIBLE TYPES 0 J NULL NULL 0
varbinary 1 J 37 NULL 0
intn 6 J 38 NULL 0
varchar 3 J 39 NULL 0
binary 1 J 45 NULL 0
char 3 J 47 NULL 0
tinyint 6 J 48 NULL 0
bit 2 J 50 NULL 0
smallint 6 J 52 NULL 0
int 6 J 56 NULL 0
smalldatetime 4 J 58 NULL 0
real 5 J 59 NULL 0
money 7 J 60 NULL 0
datetime 4 J 61 NULL 0
float 5 J 62 NULL 0
floatn 5 J 109 NULL 0
moneyn 7 J 110 NULL 0
datetimn 4 J 111 NULL 0
smallmoney 7 J 122 NULL 0
Found the following table at http://sqlzoo.napier.ac.uk/big/B10501_01/win.920/a97248/ch2.htm
I thought if I handled all the numeric and date types on the first pass I might not have to revisit this later.
Thanks again for the help. Don't be concerned about your english skills You are doing quite well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply