Tally Table vs. While Loop

  • My job requires me to parse files in various formats, and standardize them into our system's format. For this I have several script sections created, and I tweak the basics based on what I receive. A big part of this is renaming fields, removing invalid characters (i.e. - $ or " or , in data type Money fields), etc. I say all of this so you understand my overall goal. I have copied a code section (used to rename fields, replace nulls, and trim fields) I use, and I wanted to see if anyone knows a more efficient way to do this process. I have started getting into tally tables, but I don't see how (or if) these can be used to improve the process.

    The code I've provided has all needed tables (with a small amount of fake data...dealing with HIPPA restrictions), etc. My main question begins at the Declare statement.

    /* ------------- Create test tables with fake data ------------ */

    If Object_ID('dbo.[Base Adjustments]') Is Not Null

    Drop Table dbo.[Base Adjustments]

    ----------

    If Object_ID('dbo.[Base Demo Table WR2]') Is Not Null

    Drop Table dbo.[Base Demo Table WR2]

    ----------

    If Object_ID('dbo.[Base Payments]') Is Not Null

    Drop Table dbo.[Base Payments]

    Go

    --------------------

    Create Table dbo.[Base Adjustments]

    ([Column 0] Varchar(50),

    [Column 1] Varchar(50),

    [Column 2] Varchar(50),

    [Column 3] Varchar(50),

    [Column 4] Varchar(50),

    [Column 5] Varchar(50))

    ----------

    Create Table dbo.[Base Demo Table WR2]

    ([Column 0] Varchar(50),

    [Column 1] Varchar(50),

    [Column 2] Varchar(50),

    [Column 3] Varchar(50),

    [Column 4] Varchar(50),

    [Column 5] Varchar(50),

    [Column 6] Varchar(50),

    [Column 7] Varchar(50),

    [Column 8] Varchar(50),

    [Column 9] Varchar(50),

    [Column 10] Varchar(50),

    [Column 11] Varchar(50),

    [Column 12] Varchar(50),

    [Column 13] Varchar(50),

    [Column 14] Varchar(50),

    [Column 15] Varchar(50),

    [Column 16] Varchar(50),

    [Column 17] Varchar(50),

    [Column 18] Varchar(50),

    [Column 19] Varchar(50),

    [Column 20] Varchar(50),

    [Column 21] Varchar(50),

    [Column 22] Varchar(50),

    [Column 23] Varchar(50),

    [Column 24] Varchar(50),

    [Column 25] Varchar(50),

    [Column 26] Varchar(50),

    [Column 27] Varchar(50),

    [Column 28] Varchar(50),

    [Column 29] Varchar(50),

    [Column 30] Varchar(50),

    [Column 31] Varchar(50),

    [Column 32] Varchar(50),

    [Column 33] Varchar(50),

    [Column 34] Varchar(50))

    ----------

    Create Table dbo.[Base Payments]

    ([Column 0] Varchar(50),

    [Column 1] Varchar(50),

    [Column 2] Varchar(50),

    [Column 3] Varchar(50),

    [Column 4] Varchar(50),

    [Column 5] Varchar(50))

    Go

    ------------------------------

    Insert Into dbo.[Base Adjustments]

    ([Column 0],

    [Column 1],

    [Column 2],

    [Column 3],

    [Column 4],

    [Column 5])

    Select '99999999','($124.36)','12/19/2007 0:00:00','1234567','CONTRACTUAL MEDICARE ','$0.00'

    ----------

    Insert Into dbo.[Base Demo Table WR2]

    ([Column 0],

    [Column 1],

    [Column 2],

    [Column 3],

    [Column 4],

    [Column 5],

    [Column 6],

    [Column 7],

    [Column 8],

    [Column 9],

    [Column 10],

    [Column 11],

    [Column 12],

    [Column 13],

    [Column 14],

    [Column 15],

    [Column 16],

    [Column 17],

    [Column 18],

    [Column 19],

    [Column 20],

    [Column 21],

    [Column 22],

    [Column 23],

    [Column 24],

    [Column 25],

    [Column 26],

    [Column 27],

    [Column 28],

    [Column 29],

    [Column 30],

    [Column 31],

    [Column 32],

    [Column 33],

    [Column 34])

    Select '999999999','6','9876543','Doe ,John Q','M','1/27/2006 0:00:00','12/19/2007 0:00:00','1/19/1934 0:00:00','462113758','Doe','Q','John','72','348.30','HR','A','SELFPAY AFT MEDICARE','M01','MEDICARE INPATIENTS','875469321A','00','','','00','','','I','INPATIENT','INP','INPATIENT','PO BOX 1234','Some City','TX','99999','$12145.82'

    ----------

    Insert Into dbo.[Base Payments]

    ([Column 0],

    [Column 1],

    [Column 2],

    [Column 3],

    [Column 4],

    [Column 5])

    Select '88888888','($693.14)','12/19/2006 0:00:00','1313','PAYMENT MEDICARE','$0.00'

    Go

    /* ----------------- Create background tables ----------------- */ -- This starts my normal script

    If Object_ID('dbo.TempFieldNameHolder') Is Not Null

    Drop Table dbo.TempFieldNameHolder

    ----------

    If Object_ID('dbo.TempTableNameHolder') Is Not Null

    Drop Table dbo.TempTableNameHolder

    Go

    ------------------------------

    Create Table dbo.TempTableNameHolder

    (TableName Varchar(128),

    ID_Field Int

    Identity(1,1)

    Primary Key)

    Go

    ----------

    Create Table dbo.TempFieldNameHolder

    (TableName Varchar(128),

    OrgFieldName Varchar(128),

    NewFieldName Varchar(128),

    FieldType Varchar(50),

    FieldLen Int,

    ID_Field Int

    Identity(1,1)

    Primary Key)

    Go

    ------------------------------

    Insert Into dbo.TempTableNameHolder

    (TableName)

    Select 'Base Adjustments'

    Union All

    Select 'Base Demo Table WR2'

    Union All

    Select 'Base Payments'

    Go

    ----------

    Insert Into dbo.TempFieldNameHolder

    (TableName,

    OrgFieldName,

    NewFieldName,

    FieldType,

    FieldLen)

    Select 'Base Adjustments','Column 0','Monarch_AccountNo','General',50

    Union All

    Select 'Base Adjustments','Column 1','Monarch_TrnAmount','Money',50

    Union All

    Select 'Base Adjustments','Column 2','Monarch_TrnDate','Date',50

    Union All

    Select 'Base Adjustments','Column 3','Monarch_TrnCode','General',50

    Union All

    Select 'Base Adjustments','Column 4','Monarch_TrnDesc','General',50

    Union All

    Select 'Base Adjustments','Column 5','AcctBalance','Money',50

    Union All

    Select 'Base Adjustments','','Monarch_TrnType','Add',50

    Union All

    Select 'Base Adjustments','','Sheet_Name','Add',150

    Union All

    Select 'Base Demo Table WR2','Column 0','Monarch_AccountNo','General',50

    Union All

    Select 'Base Demo Table WR2','Column 1','UnknownField','General',50

    Union All

    Select 'Base Demo Table WR2','Column 2','Monarch_MRN','General',50

    Union All

    Select 'Base Demo Table WR2','Column 3','Monarch_OriginalName','General',50

    Union All

    Select 'Base Demo Table WR2','Column 4','Monarch_Gender','General',50

    Union All

    Select 'Base Demo Table WR2','Column 5','Monarch_AdmitDate','Date',50

    Union All

    Select 'Base Demo Table WR2','Column 6','Monarch_DischargeDate','Date',50

    Union All

    Select 'Base Demo Table WR2','Column 7','Monarch_BirthDate','Date',50

    Union All

    Select 'Base Demo Table WR2','Column 8','Monarch_SSN','General',50

    Union All

    Select 'Base Demo Table WR2','Column 9','Guarantor_Fname','General',50

    Union All

    Select 'Base Demo Table WR2','Column 10','Guarantor_MiddleName','General',50

    Union All

    Select 'Base Demo Table WR2','Column 11','Guarantor_Lname','General',50

    Union All

    Select 'Base Demo Table WR2','Column 12','Monarch_DRG','General',50

    Union All

    Select 'Base Demo Table WR2','Column 13','Monarch_DiagCode','General',50

    Union All

    Select 'Base Demo Table WR2','Column 14','Monarch_DishDIspCode','General',50

    Union All

    Select 'Base Demo Table WR2','Column 15','Monarch_FC','General',50

    Union All

    Select 'Base Demo Table WR2','Column 16','Monarch_FC_Desc','General',50

    Union All

    Select 'Base Demo Table WR2','Column 17','Monarch_InsCode','General',50

    Union All

    Select 'Base Demo Table WR2','Column 18','Monarch_InsName','General',50

    Union All

    Select 'Base Demo Table WR2','Column 19','Monarch_InsPolicy','General',50

    Union All

    Select 'Base Demo Table WR2','Column 20','Monarch_InsCode2','General',50

    Union All

    Select 'Base Demo Table WR2','Column 21','Monarch_InsName2','General',50

    Union All

    Select 'Base Demo Table WR2','Column 22','Monarch_InsPolicy2','General',50

    Union All

    Select 'Base Demo Table WR2','Column 23','Monarch_InsCode3','General',50

    Union All

    Select 'Base Demo Table WR2','Column 24','Monarch_InsName3','General',50

    Union All

    Select 'Base Demo Table WR2','Column 25','Monarch_InsPolicy3','General',50

    Union All

    Select 'Base Demo Table WR2','Column 26','Monarch_PatientType','General',50

    Union All

    Select 'Base Demo Table WR2','Column 27','ExtendedPatientType','General',50

    Union All

    Select 'Base Demo Table WR2','Column 28','AdmitType','General',50

    Union All

    Select 'Base Demo Table WR2','Column 29','ServiceType','General',50

    Union All

    Select 'Base Demo Table WR2','Column 30','Monarch_Address1','General',50

    Union All

    Select 'Base Demo Table WR2','Column 31','Monarch_City','General',50

    Union All

    Select 'Base Demo Table WR2','Column 32','Monarch_State','General',50

    Union All

    Select 'Base Demo Table WR2','Column 33','Monarch_Zip','General',50

    Union All

    Select 'Base Demo Table WR2','Column 34','Monarch_TotalCharges','Money',50

    Union All

    Select 'Base Demo Table WR2','','Sheet_Name','Add',150

    Union All

    Select 'Base Demo Table WR2','','Sheet_MedNum','Add',50

    Union All

    Select 'Base Payments','Column 0','Monarch_AccountNo','General',50

    Union All

    Select 'Base Payments','Column 1','Monarch_TrnAmount','Money',50

    Union All

    Select 'Base Payments','Column 2','Monarch_TrnDate','Date',50

    Union All

    Select 'Base Payments','Column 3','Monarch_TrnCode','General',50

    Union All

    Select 'Base Payments','Column 4','Monarch_TrnDesc','General',50

    Union All

    Select 'Base Payments','Column 5','AcctBalance','Money',50

    Union All

    Select 'Base Payments','','Monarch_TrnType','Add',50

    Union All

    Select 'Base Payments','','Sheet_Name','Add',150

    Go

    /* ---------------------- Rename Columns ---------------------- */

    Declare @FieldLoopCount Int,

    @MaxFieldLoopCount Int,

    @TableName Varchar(128),

    @OrgFieldName Varchar(128),

    @NewFieldName Varchar(128),

    @sql Varchar(4000)

    ----------

    Set @FieldLoopCount=1

    Set @MaxFieldLoopCount=

    (Select Max(ID_Field)

    From dbo.TempFieldNameHolder)

    ----------

    While @FieldLoopCount<=@MaxFieldLoopCount

    Begin

    Set @OrgFieldName=''

    ----------

    Set @OrgFieldName=

    (Select OrgFieldName

    From dbo.TempFieldNameHolder

    Where ID_Field=@FieldLoopCount

    And FieldType<>'Add')

    --------------------

    If @OrgFieldName<>''

    Begin

    Set @TableName=

    (Select TableName

    From dbo.TempFieldNameHolder

    Where ID_Field=@FieldLoopCount)

    ----------

    Set @NewFieldName=

    (Select NewFieldName

    From dbo.TempFieldNameHolder

    Where ID_Field=@FieldLoopCount)

    --------------------

    Set @sql=

    'Exec sp_Rename ''dbo.[' + @TableName + '].[' + @OrgFieldName + ']'',''' + @NewFieldName + ''',''Column'''

    Exec(@SQL)

    ----------

    Set @sql=

    'Update dbo.[' + @TableName + ']

    Set [' + @NewFieldName + ']=''''

    Where [' + @NewFieldName + '] Is Null'

    Exec(@SQL)

    ----------

    Set @sql=

    'Update dbo.[' + @TableName + ']

    Set [' + @NewFieldName + ']=Ltrim(Rtrim([' + @NewFieldName + ']))'

    Exec(@SQL)

    End

    --------------------

    Set @FieldLoopCount=@FieldLoopCount + 1

    End

    Go

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • It looks to me like loading the data into pre-built tables with the correct column names and all that would be easier.

    Is there a reason that you have to go through all this renaming and all that, instead of just having target tables that already have all the rules and names built into them?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • We receive the data in .csv format. Each state's Medicaid department, and the federal Medicare department are all standard unto themselves (in short 52 different formats). I have those pre-defined, and I've found it is faster to process those this way.

    The hospitals (which is what this data represents) each have their own format (so far I've counted over 400), and sometimes they change formats from 1 year to the next.

    The fastest way I've found of matching up their field names to ours is to pull a list of their names into Excel, and match them up manually. From there I use Excel to build the Select section of the Insert statement. it takes me maybe 5 minutes to complete all of that.

    The best way to do this would be with a custom tool which would allow us to quickly map the received field names to our field names. I was familiar with VB 6, and am learning VB .Net in spurts. That will be developed eventually. Until then, this has been the fastest means I've found.

    Looking at all of the options, the original question still stands. Even with pre-named fields I would still need to iterate through them to set all date fields into Convert(Varchar,Convert(DateTime,@NewFieldName),112) Where IsDate(@NewFieldName)=1, or to run a Replace(@NewFieldName,'$','') on all money fields. Is there a way to do that more efficiently than I have it going now?

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • Okay. That matches what I expected.

    Have you looked into OpenRowset? It can read data directly from Excel or CSV files into a staging table.

    The staging table might have typed columns, or might be SQLVariant datatypes, or NVarChar or something along those lines.

    Let's say you have two file formats (for the sake of brevity).

    One has these columns:

    FirstName (string)

    LastName (string)

    SomethingThatsADate (datetime)

    Cash0 (money as a string)

    The other has:

    NameLast (string)

    NameFirst (string)

    NameMiddle (string)

    SomethingElseThatsADate (datetime)

    Cash1 (money as a string)

    Cash2 (money as a string)

    If you create a table with:

    ID int primary key

    NameFirst varchar

    NameMiddle varchar

    NameLast varchar

    SomethingThatsADate date

    SomethingElseThatsADate date

    Cash0 float/decimal/money

    Cash1 float/decimal/money

    Cash2 float/decimal/money

    You can insert from either of these into that table quite easily, but building either a mapping table (probably best if the input formats vary frequently) or by determining which columns to specify based on something like the file name, file directory, etc.

    Insert into dbo.MyStagingTable (NameFirst, NameMiddle, NameLast, SomethingThatsADate, SomethingElseThatsADate,

    Cash0, Cash1, Cash2)

    select FirstName, NULL, LastName, SomethingThatsADate, NULL, Cash0, NULL, NULL

    from OpenRowset( file specification here);

    You can build your data hygiene right into the Select clause. Wrap the necessary Replace functions around the Cash0 column, for example, or build a UDF for that.

    The advantage to a system like this is that you can automate it almost completely. I built one that worked this way that had to deal with importing over 200 different file formats, some text, some CSV, some Excel. By building a mapping table, and doing the OpenRowset command through dynamic SQL, all I ever had to do was deal with new file formats by mapping them into the table. Since OpenRowset has to be dynamic SQL anyway to deal with dynamic file names, that was easy. A VB.NET CLR function would pull in a list of files to import, and then step through them one by one, calling the dynamic insert each time, and boom, it was done. Never had to open a file myself unless they changed the metadata. Once or twice a year for the imports, usually when we got a new customer with a new format.

    Without VB.NET, you could do the same thing with any other means of getting a list of files into a cursor of some sort. SSIS could do that for you with a For Each Next loop, for example. XP_CmdShell can do it also, though with the usual security caveats for that.

    Then, once you have the input engine working, you define Views on the table, and every hospital that needs its own output format just gets a View defined for them. If they want to call "NameFirst", "PersonalNameThatHeGoesBy", no problem, alias the column in the View. Change their needs? Don't rebuild your import or anything like that, just change the definition of the View. Then you can use BCP or any number of other tools (including SSIS) to output from the Views, or whatever it is you need to do with those.

    That builds a system with minimal human intervention in the data stream. Works beautifully. Less error prone, too.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I realize this doesn't actually help provide a solution to your actual question, but I've been through this before, albeit non-medical. And, trust me, there is no valid solution unless there is consensus as to a specific file format definition. If the supplier of the file is free to change the format at will, then you will always have problems. You can hope that they column names don't change, but when they do, a mapping solution will fail too.

    Better to agree upon a format and then use suitable tool for the job (BCP, SSIS, etc).

  • Lamprey13 (5/25/2011)


    I realize this doesn't actually help provide a solution to your actual question, but I've been through this before, albeit non-medical. And, trust me, there is no valid solution unless there is consensus as to a specific file format definition. If the supplier of the file is free to change the format at will, then you will always have problems. You can hope that they column names don't change, but when they do, a mapping solution will fail too.

    Better to agree upon a format and then use suitable tool for the job (BCP, SSIS, etc).

    A mapping solution will fail if the source columns change names, but it's easy enough to send an alert at that time, and update the mapping.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Can we address above issue or Senario by using SSIS Packages ?

    We are receiving CSVs files where order of column changed but is correct.

    for e.g.

    file1 columns0 columns1 columns2

    file2 columns1 columns0 columns2

    file3 columns2 columns1 columns0

  • vineet_dubey1975 (5/26/2011)


    Can we address above issue or Senario by using SSIS Packages ?

    We are receiving CSVs files where order of column changed but is correct.

    for e.g.

    file1 columns0 columns1 columns2

    file2 columns1 columns0 columns2

    file3 columns2 columns1 columns0

    It's not as easy in SSIS, because the data flow object definitions can't be built at runtime. It has to have columns, et al, predefined.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I appreciate all of the ideas that have been shared on here, and I look forward to learning about the fundamentals of most / all of them. I thought I knew what I was doing, but being amongst people of your caliber has made me realize how much I still have to learn.

    Since I know it will be a little while before I am able to learn several of the techniques you offered (and because I have an in general curiosity about the challenge) I would like to restate my initial question. I have seen a tally table used like this (direct rip off of script I read to learn about tally tables):

    ----------------------------------------

    /* Examples of how to use Tally vs. Looping RBAR style */

    --===============================================

    -- Display the count from 1 to 10

    -- using a loop.

    --===============================================

    --===== Declare a counter

    DECLARE @N INT

    SET @N = 1

    --===== While the counter is less than 10...

    WHILE @N <= 10

    BEGIN

    --==== Display the count

    SELECT @N

    --==== Increment the counter

    SET @N = @N + 1

    END

    Go

    --------------------

    --===============================================

    -- Display the count from 1 to 10

    -- using a Tally table.

    --===============================================

    SELECT N

    FROM dbo.Tally

    WHERE N <= 10

    ORDER BY N

    Go

    Is there a way to use this logic to iterate through fields, or (based solely on a choice between the two) does one have to use the while loop logic? I primarily ask to learn more about using a tally table, when a tally table can't be used, different tactics used in deploying a tally table, etc. I approaching this from a knowledge for the sake of knowledge point of view. Does that help explain my question a little better?

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • It depends on what you want to do.

    If, for example, you want to perform a set of update/delete/insert actions based on iterative needs, then a Tally/Numbers table can do it better than the While loop. Take a look at Jeff Moden's articles on this site regarding Tally tables for some good examples. Also on simple-view.com, look for Phil and Robyn's article on Helper tables.

    If, on the other hand, you need to run a stored procedure once for each row, then a Tally table really won't help you replace a loop.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I'm getting stuck on how to script the tally version. This is what I have come up with, but so far it's a no go.

    Declare @TableName Varchar(128),

    @FieldName Varchar(128),

    @sql Varchar(4000)

    Set @TableName=

    (Select TableName

    From dbo.TempFieldNameHolder TFNH Inner Join dbo.TallyTable TT

    On TFNH.ID_Field=TT.ID_Field)

    Set @FieldName=

    (Select FieldName

    From dbo.TempFieldNameHolder TFNH Inner Join dbo.TallyTable TT

    On TFNH.ID_Field=TT.ID_Field)

    Set @sql=

    'Update dbo.@TableName

    Set @FieldName=Replace(@FieldName,''$'','''')'

    Exec(@SQL)

    This code is based off the code from the first post.

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • jarid.lawson (5/27/2011)


    I'm getting stuck on how to script the tally version. This is what I have come up with, but so far it's a no go.

    Declare @TableName Varchar(128),

    @FieldName Varchar(128),

    @sql Varchar(4000)

    Set @TableName=

    (Select TableName

    From dbo.TempFieldNameHolder TFNH Inner Join dbo.TallyTable TT

    On TFNH.ID_Field=TT.ID_Field)

    Set @FieldName=

    (Select FieldName

    From dbo.TempFieldNameHolder TFNH Inner Join dbo.TallyTable TT

    On TFNH.ID_Field=TT.ID_Field)

    Set @sql=

    'Update dbo.@TableName

    Set @FieldName=Replace(@FieldName,''$'','''')'

    Exec(@SQL)

    This code is based off the code from the first post.

    My apologies... I don't really understand what you're trying to do. Could you give a small example with some real column names? I'm also not sure that a Tally table would be beneficial here but one never knows until the deed is done.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jarid.lawson (5/27/2011)


    I'm getting stuck on how to script the tally version. This is what I have come up with, but so far it's a no go.

    Declare @TableName Varchar(128),

    @FieldName Varchar(128),

    @sql Varchar(4000)

    Set @TableName=

    (Select TableName

    From dbo.TempFieldNameHolder TFNH Inner Join dbo.TallyTable TT

    On TFNH.ID_Field=TT.ID_Field)

    Set @FieldName=

    (Select FieldName

    From dbo.TempFieldNameHolder TFNH Inner Join dbo.TallyTable TT

    On TFNH.ID_Field=TT.ID_Field)

    Set @sql=

    'Update dbo.@TableName

    Set @FieldName=Replace(@FieldName,''$'','''')'

    Exec(@SQL)

    This code is based off the code from the first post.

    Since that code won't compile, I'm not sure what it's meant to do. Are you trying to iterate through a list of tables and columns and come up with an update statement for each one?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Sorry for not making my goal clear enough. In short I am trying to iterate through several columns either on the same or multiple tables, and run an update on each of these columns. An example of the columns could be:

    TableName | ColumnName | ColumnType

    dbo.Table1 | AdmitDate | Date

    dbo.Table1 | TotalCharges | Money

    dbo.Table1 | AccountNo | General

    dbo.Table2 | AccountNo | General

    dbo.Table2 | Deductible | Money

    dbo.Table2 | TransDate | Date

    I need to go through this list and update all date fields into one format, all money fields into another format, and all general fields need to stay as a reference for all fields being trimmed, and all nulls replaced with ''. As an example I've included some basic updates I run for each ColumnType and 1 field from each table:

    Update dbo.Table1

    Set AdmitDate=Convert(Varchar,Convert(DateTime,AdmitDate),112)

    Where IsDate(AdmitDate)=1

    ---------------

    Update dbo.Table1

    Set AdmitDate=''

    Where IsDate(AdmitDate)=0

    ----------------

    Update dbo.Table2

    Set Deductible=Replace(Deductible,'$','')

    Where Deductible Like '%$%'

    -----------------

    Update dbo.Table2

    Set Deductible=Replace(Deductible,'"','')

    Where Deductible Like '%"%'

    ---------------

    Update dbo.Table2

    Set Deductible=Replace(Deductible,',','')

    Where Deductible Like '%,%'

    ----------------

    Update dbo.Table2

    Set Deductible=Replace(Deductible,')','')

    Where Deductible Like '%)%'

    -----------------

    Update dbo.Table2

    Set Deductible=Replace(Deductible,'(','-')

    Where Deductible Like '%(%'

    --------------------

    Update dbo.Table2

    Set Deductible='0.00'

    Where IsNumeric(Deductible)=0

    If you go back to my very first post in this chain you can see a full version of what I'm doing if that makes things a little easier. I'm just not sure if a tally table would work in a situation like this, or if leaving the loop logic in place would be better.

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • May I suggest as an optimization fewer but more complex UPDATE statements, to reduce the amount of I/O?

    As a very rough example, the first two statement could collapse to:

    Update dbo.Table1

    Set AdmitDate= CASE WHEN IsDate(AdmitDate)=1 THEN Convert(Varchar,Convert(DateTime,AdmitDate),112)

    ELSE ''

    END

    Then you can collapse the next set, and then, since there isn't a WHERE clause anymore, you can update more than one field in the same UPDATE and get a bunch of work done each pass through the table.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply