Split data

Question

Split data

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 1

You already said that... this is the third time... relax... someone else will answer...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Here you go:

Create proc spTable_LoadDynamic (@TableName as sysname, @hdr as varchar(max), @data as varchar(max)) as

/*

Dynamically load a table from a reformatted Excel export file.

13-July-2008, RBarryYoung

Test script:

declare @headers as varchar(max), @file as varchar(max)

Set @headers = 'customer_id~customer_name~Address1~Address2~City~State~Pincode~Age'

--Data_String:

Set @file = '1~Name1~Address1~Address2~Chennai~Tamil Nadu~5744856~85#

2~Name2~Address2~Address3~Madurai~Tamil Nadu~57448562~80#

3~Name3~Address3~Address4~Mysore~Karnataka~570007~79#

4~Name4~Address4~Address5~Bangalore~Karnataka~570007~52#

5~Name5~Address5~Address6~Belgum~Karnataka~570006~74#

6~Name6~Address6~Address7~Delhi~Delhi~5700085~64#

7~Name7~Address7~Address8~Ramnagar~Karnataka~570042~41'

exec spTable_LoadDynamic 'Footst1', @headers, @file

*/

--======create the table

declare @sql nvarchar(max)

Set @sql = 'Drop Table '+@TableName

print @sql

Exec (@sql)

Set @sql = 'Create Table '+@TableName+'('+Replace(@hdr, '~', ' varchar(200), ')+' varchar(200))'

print @sql

Exec (@sql)

--Load the values

--INSERT into foost

-- Select case

Declare @prefix nvarchar(max)

Set @prefix = 'Insert into '+@TableName

Set @sql = 'INSERT into '+@TableName+'('+Replace(@hdr, '~', ',')+')

Select '''+Replace( Replace(@data, '~', ''','''), '#

', '''

UNION ALL Select ''' )+''''

print @sql

Exec(@sql)

go

--==--==--==

declare @headers as varchar(max), @file as varchar(max)

Set @headers = 'customer_id~customer_name~Address1~Address2~City~State~Pincode~Age'

--Data_String:

Set @file = '1~Name1~Address1~Address2~Chennai~Tamil Nadu~5744856~85#

2~Name2~Address2~Address3~Madurai~Tamil Nadu~57448562~80#

3~Name3~Address3~Address4~Mysore~Karnataka~570007~79#

4~Name4~Address4~Address5~Bangalore~Karnataka~570007~52#

5~Name5~Address5~Address6~Belgum~Karnataka~570006~74#

6~Name6~Address6~Address7~Delhi~Delhi~5700085~64#

7~Name7~Address7~Address8~Ramnagar~Karnataka~570042~41'

exec spTable_LoadDynamic 'Footst1', @headers, @file

go

Select * from Footst1

Note I have included a demonstration script at the end.

Note also that this approach might need some adjustment if there are embedded apostrophes or also if the data is "ragged-right", but neither of these should be too difficult.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

JoyKing Ten Centuries Points: 1106 More actions · Answer 3

Awesome , Its working fine...Thanks for all you supports..

mish_b20 Grasshopper Points: 18 More actions · Answer 4

You can use XML Datatype and put the string that you need into the column as xml, so you don't have ant problems getting it back and translating it to what ever structure that you like/need .

Michael

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 5

I know disk space is relatively cheap... I just can't bring myself to suffer the overhead of storing XML in a database anywhere except a temporary staging table. With respect to the load on the "pipe", I also prefer not to use it for passing parameters, either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

einman33 SSCrazy Points: 2813 More actions · Answer 6

XML is pure evil in the data tier. I don care if XPATH make my job 1000% easier to wade through 500k xml blobs. A realtional engine should not have to deal with markup code. TDS protocol is not optimized for XML. Haphazard use of XML is costing our industry millions of phyiscal dollars.

Stop the insanity :w00t:

sorry for the rant, I am very anti-XML 🙁

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 7

Heh... I feel the same... but I said "Today, just this one day, I shall not rant." 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

einman33 SSCrazy Points: 2813 More actions · Answer 8

einman33

SSCrazy

Points: 2813

July 15, 2008 at 12:10 pm

#843313

🙂 Rants cary over, so you get two tommorrow.

Vijaya Kadiyala SSCrazy Eights Points: 9149 More actions · Answer 9

Hi,

Basically you need to use Split functiont to break the string.

http://www.dotnetspider.com/resources/1266-Split-e-String-Using-char-separator.aspx

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 10

I wouldn't recommend that way... it has a While loop in it and a couple of other performance related problems. Try the following, please... it also explains how it works... split code is near the end...

http://www.sqlservercentral.com/articles/TSQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)