Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 processing multiple items on each input row
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
1 Posts

Posted - 01/31/2014 :  12:46:03  Show Profile  Reply with Quote
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.

  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000