Extracting from a very wide table

Question

Extracting from a very wide table

aaa121

SSCertifiable

Points: 5902
More actions
December 14, 2009 at 12:04 pm

#207363

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 +/-.

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

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

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.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

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]

aaa121 SSCertifiable Points: 5902 More actions · Answer 3

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?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

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]

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 5

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)

aaa121 SSCertifiable Points: 5902 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 7

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)

aaa121 SSCertifiable Points: 5902 More actions · Answer 8

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!

aaa121 SSCertifiable Points: 5902 More actions · Answer 9

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 10

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