Insert Rows from a Parsed Field into Another Table

Question

Post reply

Insert Rows from a Parsed Field into Another Table

susan.howe

Grasshopper

Points: 20
More actions
April 12, 2010 at 2:09 pm

#233868

Does anyone have suggestions on the most efficient way to get the following data from one table into to other tables, as described below?
Table1
Name Category
-------- -----------------------------------------
ABCXYZ Phylum:Class:Order:Family:Genus:Species
DEFGHI Continent:Country:State:City
...
Table2
ID Name
---- ---------
101 ABCXYZ
102 DEFGHI
Table3
ID Type Value
--- ------ ----------
101 Attrib1 Phylum
101 Attrib2 Class
101 Attrib3 Order
101 Attrib4 Family
101 Attrib5 Genus
101 Attrib6 Species
102 Attrib1 Continent
102 Attrib2 Country
102 Attrib3 State
102 Attrib4 City
The category entries can vary in depth.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Take a look at this article from Jeff Moden, read the "split" section.

http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

Since you seem to have a fairly solid delimiter pattern going, it should work well for your purpose.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jus SSCrazy Points: 2536 More actions · Answer 2

Try below code..you need to have tally table built in your database to run this..

SET NOCOUNT ON

if object_id('tempdb..#Category') IS NOT NULL DROP TABLE #Category

if object_id('tempdb..#Category1') IS NOT NULL DROP TABLE #Category1

if object_id('tempdb..#Category2') IS NOT NULL DROP TABLE #Category2

CREATE TABLE #Category (

CID int primary key identity not null

,CName nvarchar(100) ,

Category varchar(100)

)

CREATE TABLE #Category1 (

CID int not null

,CName varchar(100))

CREATE TABLE #Category2 (

CID int not null,

Category varchar(100))

insert into #Category values('ABCXYZ','Phylum:Class:Order:Family:Genus:Species')

insert into #Category values('DEFGHI','Continent:Country:State:City')

insert into #Category1 Select CID,Cname from #Category

insert into #Category2 select

cid, SUBSTRING(':'+Category+':',N +1,CHARINDEX(':',':'+Category+':',N +1)-N -1) AS Category

FROM #Category

cross join dbo.Tally

WHERE

N < LEN(':'+Category+':')

AND SUBSTRING(':'+Category+':',N ,1) = ':'

--Final result

select * from #Category1

select * from #Category2

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

{edit} Machine dependent code removed. Sorry folks.

I ran into a bit of a buzz saw with the new split function that I previously posted (and have removed from this thread). It turns out that it's quite machine dependent. I ran the new code on a million row split at work and it came in at 4:18. To see how much faster it was than the old code, I ran the old code on the identical scenario and data... it came in at 3:48!!! In other words, the old code similar to what Wayne used came in a full 30 seconds faster on a lousy million rows.

Go back to the old code, Wayne. It's not machine dependent like the new code seems to be.

I appologize for the confusion.

--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)

susan.howe Grasshopper Points: 20 More actions · Answer 4

Thanks for all the help guys. I'll try these suggestions and see how they work.

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