I need to normalize (if that's the right word) a very wide table with more than 100 million records, turning it into a pair
of relational tables. For many variables in the table, there are 20 fields that could be filled in. For simplicity's sake, the table looks like this:
(so on, up to Value20)
The way the table is currently set up, if I want to find records where "Value" equals 'x', I need to search across 20 fields. I want to create a table that has one record for each of the 20 Value fields that are filled in, ignoring those that are
The only way I know to do that is something like below, which can take 20 hours on my table. Is there a more efficient (faster) way to do this? Thanks in advance for any advice.
DECLARE @sql varchar(Max)
WHILE (@step < 21)
DECLARE @table varchar(500)
SET @table ='Receptacle'
DECLARE @step int
set @step = 1
SET @sql = '
INSERT INTO ' + @table +'
JoinID, Step, Value
' + CONVERT(varchar(2),@step) + ' as Step,
Value' + CONVERT(varchar(2),@step) +' as HCFPCL
WHERE VALUE' + CONVERT(varchar(2),@step) +' is not null
View Complete Post