data from textfile(vertical aligned) to sql table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

data from textfile(vertical aligned) to sql table?

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2008-11-05 : 17:19:34

Hello All

in my source file i have data as horizontal (not like regular file)

example

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

can any one give me some idea how can i load this file to the sql server table

Thanks in advance
asin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-06 : 03:06:58

[code]
create table test_data(data varchar(1000),value varchar(1000))

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

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

Insert into actual_table(AccountNo,AccountName,City)
select t1.value as AccountNo, t2.value as AccountName, t3.value as City from
(
select id,value from test_Data
where id%3=1
) as t1
inner join
(
select id,value from test_Data
where id%3=2
) as t2 on t1.id+1=t2.id
inner join
(
select id,value from test_Data
where id%3=0
) as t3 on t1.id+2=t3.id

select * from actual_table
[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources