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://qa.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: 2530 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: 1000539 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)
Intro to Tally Tables and Functions

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

susan.howe

Grasshopper

Points: 20

April 15, 2010 at 10:14 am

#1152268

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

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