May 25, 2011 at 10:56 am
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]
May 25, 2011 at 11:33 am
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
May 25, 2011 at 11:43 am
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]
May 25, 2011 at 1:25 pm
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
May 25, 2011 at 1:42 pm
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).
May 25, 2011 at 2:06 pm
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
May 26, 2011 at 2:01 am
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
May 27, 2011 at 7:05 am
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
May 27, 2011 at 8:04 am
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]
May 27, 2011 at 9:32 am
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
May 27, 2011 at 1:50 pm
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]
May 27, 2011 at 9:51 pm
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
Change is inevitable... Change for the better is not.
May 31, 2011 at 6:17 am
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
May 31, 2011 at 7:57 am
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]
June 1, 2011 at 8:29 am
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