Home Forums SQL Server 2008 SQL Server Newbies Setting a Primary key to subsequential Data (Header - Line) RE: Setting a Primary key to subsequential Data (Header - Line)

  • Ah, the old mainframe data sort issue. I HATE THEM. However, the solution isn't rediculously hard... as long as you're not loaded to SQL yet.

    This takes a bit of chicanery mostly because the system styles are so different. As you've already mentioned, you'd like to get this into a table hierarchy. The problem is the pre association.

    The solution to this is during the load sequence, as mentioned above. This is a place where SSIS can shine compared to some of the more traditional T-SQL methods. Using a synchronous transformation script component, you can read the records through in the order from the file and assign a new column an identifier (GUIDs in this case are probably easiest for first pass, though an int can be easily used to staging tables as well) . What you'll do is detect each row as it comes through, and when the row type is Header you'll change the identifier and assign it to each row that comes through until you hit the next header. This sounds more complicated than it really is, but you need a little (very little, I can do it) familiarity with VB or C# Coding.

    If the data is already table loaded, this gets uglier, mostly because you've probably lost any chance of knowing what data belongs where. Hopefully you still have the original load files. If not, you're in for a headache. Script the data out during table scripting (it's an option in SSMS) and dump that to a new query page. Now, manually (because you have to check the actual data against the original ANYWAY) add in your identifier column and corresponding values to the 'everything in one' table. This will give you a new staging structure that will allow you to feed your two proper tables. Once that's done you should be able to modify your import process to never have to do that again.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA