Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Phil123789
Starting Member
1 Post |
 Posted - 2014-01-31 : 12:46:03
|
| Hi,The situation is as follows: we have approximately 80 million HL7 messages to be loaded into SQL Server 2008 (standard edition). These 80 million records are split into text files containing 50,000 records on average. HL7 messages - for those who don’t know - are widely used in the healthcare industry, a single message could contain for example, demographic information and information concerning a hospital appointment, in other words there could be scores of different items of information contained within a single message. These 80 million messages are to be loaded into SQL Server and the information within each message stripped out to populate various columns within a single table.The general processing flow will probably go as follows: (1) load a file into a staging table (HL7_IN) in SQL Server (2) process each row in the staging table then insert it into another table (HL7_OUTPATIENTS). Or maybe just load the data directly into HL7_OUTPATIENTS then update all newly inserted rows. Having looked on numerous forums and blogs the consensus seems to be that cursors should not be used, ‘set based’ processing should be used instead. I can understand how this would work where there isn’t much processing to be done on each row but we have an equivalent system running in Oracle and each row requires a couple of hundred lines of code to strip out all items of information (lots of instrings and substrings) and populate relevant columns. So the question is – what is the best way of doing things in SQL Server - I know I could use a cursor and maybe this is the only way given the amount of processing there is for each row but I would be interested to know if there are any other – faster, more efficient alternatives.Apologies for the detailed question – all ideas appreciated.Phil |
|
|
|
|
|
processing multiple items on each input row