One of the users asked this question in the SQL forum

I have data in the text as follows


AccountNo: 00234543
AccountName: Kickser
City: Chicago
Country:USA
AccountNo: 00234543
AccountName: Annis
City: Seatle
Country:USA
AccountNo: 12234456
AccountName: pargenezzer
City: NYC
Country:USA
...
....
.....
AccountNo: 12233477
AccountName: PILIP
City: Edison
Country:USA

How do I store data to SQL Server table that has columns AccountNo,AccountName,City 
which is normalised?

Here is one of the methods that does it

--Create statging table to load data

create table test_data(data varchar(1000),value varchar(1000))

GO

--Load data from text file to staging table

BULK INSERT test_data FROM 'file_path'  WITH 
(
FIELDTERMINATOR =':',
ROWTERMINATOR ='\n'
)

GO

--Add identity column

alter table test_Data add id int identity(1,1)

GO

--Do process and convert each column value to multiple columns

declare @count int,@sql varchar(8000),@columns varchar(1000),@i int 

select @count=count(distinct data) from test_Data
select @sql='', @i=1

while @i<=@count
Begin
        select @sql=@sql+'select id,value as value'+cast(@i as varchar(10))+' from test_Data where id%'+cast(@count as 
        varchar(2))+'='+cast(case when @i=@count then 0 else @i end as varchar(2))+') as t'+cast(@i as varchar(2))+
        case when @i>1 then ' on t1.id+'+cast(@i-1 as varchar(2))+'='+'t'+cast(@i as varchar(2))+'.id' else '' end+
        case when @i<>@count then ' inner join (' else '' end,
        @columns=coalesce(@columns+',','')+'t'+cast(@i as varchar(2))+'.value'+cast(@i as varchar(10))

        select @i=@i+1

End

--Show the result

select @sql='select '+@columns+' from 
('+@sql

exec(@sql)

Now the result can be copied to another normalised table