Extracting from a very wide table

  • I need to extract and stage a mainframe table which has 1000+ columns into SQL Server. Do you guys have ANY advice on the most efficient method to do this?

    The table is so wide as each numeric value has a additional column with the sign for the associated figure +/-.

  • Sounds awful... my sympathies.

    Anyway, I might partition the table into multiple narrower tables that can be linked on an ID column. To do this you can use SSIS but you might have to write some custom pre-processing software.

    The probability of survival is inversely proportional to the angle of arrival.

  • While waiting for some one with a better understanding of data storage you might while away your time reading the following, in particular how the database engine stores VARCHAR(Max) data.

    http://msdn.microsoft.com/en-us/library/ms178158.aspx

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks both. I am glad there wasn't something obvious I was missing! My thought was to partition into smaller tables and use a view over the top so this seems like my best option at the moment.

    I'll check out the varchar(max) article shortly and see if it will prove useful. Were you thinking of storing multiple columns from the source into a single column of this data type?

  • Were you thinking of storing multiple columns from the source into a single column of this data type?

    Not knowing your actual data and its sequence across a row of input, wether the input could be logically split into different fields or rows yet still knowing how it is eventually tied together... etc., etc., I thought I would point you to a possible idea, an idea that might trigger an "out of the box" answer in your mind.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • aaa-322853 (12/14/2009)


    I need to extract and stage a mainframe table which has 1000+ columns into SQL Server. Do you guys have ANY advice on the most efficient method to do this?

    The table is so wide as each numeric value has a additional column with the sign for the associated figure +/-.

    Are most of the values numeric in nature?

    --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)

  • I believe so yes. I am yet to actually see the data. At the moment I just know it is a 'big' issue so I am trying to get ahead of the game.

  • If all the "fields" in this monster table are all in good order, then this shouldn't be a problem at all. I haven't had to look it up in a good long while but I believe that SQL Server allows for up to 1024 columns per table. Even if the monster mainframe table comes to you in the form of file (fixed field, delimited, whatever), SQL Server has some pretty handy tools for splitting the data up and importing it into tables. In those areas where it may fall short, the ol' human brain can do some pretty remarkable things with this.

    Unless they've done something totally whacky (and the separate sign columns aren't whacky) with the data, I believe this could be a piece of cake.

    If you really want to get a leg up on this, ask them for 10 rows of sample data and we'll help you start picking it apart.

    --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)

  • Thanks Jeff, some good points.

    What I have managed to find out is that the table actually has in excess of 6k columns! However, only 1k of these are useful.

    I think the hardest thing will be writing the select statement to grab the data!

  • Is it possible to store more than the 1,024 columns in SQL Server 2008?

  • aaa-322853 (2/9/2010)


    Is it possible to store more than the 1,024 columns in SQL Server 2008?

    Yes, you can use wide tables which allow up-to 30,000 columns look for more in Books Online.

    There is performance consideration that needs to be considered..


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

Viewing 11 posts - 1 through 10 (of 10 total)

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