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

     

    Fixed-Length Data TypeVariable-Length Data Type

    CHAR

    VARCHAR

    NCHAR

    NVARCHAR

    BINARY

    VARBINARY

    DATETIME, SMALLDATETIME

    DATETIMN

    FLOAT

    FLOATN

    INT, SMALLINT, TINYINT

    INTN

    DECIMAL

    DECIMALN

    NUMERIC

    NUMERICN

    MONEY, SMALLMONEY

    MONEYN

     

    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