DB Design And Data Retrieval From A Heavy Table

I have a requirement to have 612 columns in my database table. The # of columns as per data type are: BigInt – 150 (PositionCol1, PositionCol2…………PositionCol150) Int - 5

Solution 1:

Don't stick with your current design. Your repeating groups are unweildy and self limiting... What happens when somebody uploads 151 times? Normalise this table so that you have one of each type per row rather than 150. You won't need mapping this way as you can select SAPDATA from the positioncol without worring if it is 1-150.

You probably want a PROJECTS table with an ID, a PROJECT_UPLOADS table with an ID and an FK to the PROJECTS table. This table would have Position, SourceDate, Formula and IsActive given your use-case above.

Then you could do things like

select p.name, pu.position from PROJECTS p inner join PROJECT_UPLOADS pu on pu.projectid = p.id WHERE pu.position = 'SAPDATA'

etc.