Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Import from Text file - Single column to multiple columns

May 5 2009 10:37AM by Madhivanan   

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

Tags: t-sql, sql_server, bulk_insert,


Madhivanan
3 · 40% · 12968
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Import from Text file - Single column to multiple columns" rated 5 out of 5 by 1 readers
Import from Text file - Single column to multiple columns , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]