Loop, Cursor or maybe something else?

• perryj5

  Grasshopper

  Points: 22

  More actions

  October 20, 2009 at 1:51 pm

  #215776

  I have a sql table that is the product of an imported text file. The text file represents a parent child relationship using fixed space. Unfortunately I have no control over the file, but I wanted to design a solution using SSIS and SQL.

  The file imports from a SSIS package to a table like this:

  col1

  -----

  1 Cars

  a Corvette

  b Porche

  c Ferrari

  2 Tanks

  a Sherman

  3 Boats

  a Yacht

  b Sailboat

  etc...

  The required output is building a table like this:

  Col1 | Col2

  -----------

  1 Cars | a Corvette

  1 Cars | b Porche

  1 Cars | c Ferrari

  2 Tanks | a Sherman

  3 Boat | a Yacht

  3 Boat | b Sailboat

  I imagine there's some kind of looping control structure that would work well here in sql. Thanks!

• GSquared

  SSC Guru

  Points: 260824

  More actions

  October 20, 2009 at 1:55 pm

  #1068498

  Since rows in a relational database don't have a definite order, you'll need to do this in preprocessing.

  I've seen systems that use .NET code to step through the text file and add the data to arrays, then use those to populate the SQL tables correctly, in similar situations.

  If you try to do this in T-SQL after the import, you could probably achieve something that looks like it works if you have an ID column on the table and use that to join from the sub-item to the highest ID category with an ID below it. That'll be slow (depending on the amount of data), and it will probably give the illusion of working, but it really isn't reliable in the long run.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Jeff Moden

  SSC Guru

  Points: 1003913

  More actions

  October 20, 2009 at 10:37 pm

  #1068663

  Bulk Insert and BCP will bring things in in the same order as the file unless done in parallel. Make the table with an IDENTITY column and BULK INSERT into a view that doesn't have the IDENTITY column, or use a BCP Format File to skip the IDENTITY column. Then use MOD 2 with a bit of a cross tab to swing the tables up.

  Another (very fast) way is to use the BCP format file to read two lines at a time by using the EOL as a delimiter for the 2nd "field".

  EDIT: Misread the file data... That which has not been struck out above, still applies though.

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

• Dave Ballantyne

  SSC-Dedicated

  Points: 33566

  More actions

  October 20, 2009 at 10:54 pm

  #1068668

  Im no SSIS expert , but you can use variables inside it.

  So i would suggest creating one as a rowid counter increment on each row, and another as a "groupid" which will contain the value of the last numerical key column.

  ---

  
  Clear Sky SQL
  My Blog[/url]

• Jeff Moden

  SSC Guru

  Points: 1003913

  More actions

  October 21, 2009 at 12:21 am

  #1068679

  Apologies... I modified my previous post because I misread the file data.

  Let us know when you get the data loaded (several methods for that have already been offered) with a correctly sorted RowNum and one of us will show you how to "smear" the parents down through the children one way or another.

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

• sqlride

  Old Hand

  Points: 324

  More actions

  October 21, 2009 at 5:53 pm

  #1069203

  Hi

  i hope this can help u, maybe not is the good way but i thing that works!

  create table #tmpTable(

  IdTmpTableINT IDENTITY(1,1),

  ConceptVARCHAR(50)

  )

  create table #tmpProduction

  (

  idProductionINT IDENTITY (1,1),

  idConceptVARCHAR(50),

  ConceptVARCHAR(50)

  )

  insert into #tmpTable(Concept)

  select'1 Cars'

  insert into #tmpTable(Concept)

  select'a Corvette'

  insert into #tmpTable(Concept)

  select'b Porche'

  insert into #tmpTable(Concept)

  select'c Ferrari'

  insert into #tmpTable(Concept)

  select'2 Tanks'

  insert into #tmpTable(Concept)

  select'a Sherman'

  insert into #tmpTable(Concept)

  select'3 Boats'

  insert into #tmpTable(Concept)

  select'a Yacht'

  insert into #tmpTable(Concept)

  select'b Sailboat'

  DECLARE@intCounterINT,

  @intMaxRowsINT

  declare@intNumberint,

  @vchNumberchar(2),

  @vchConceptsVARCHAR(50),

  @intIDINT,

  @intIDConceptVARCHAR(50),

  @intIdNewConceptVARCHAR(50)

  SELECT @intMaxRows = COUNT(*)

  FROM#tmpTable

  -- BEGIN USING THE LOOP

  SELECT @intCounter = 1

  WHILE @intCounter <= @intMaxRows

  BEGIN

  -- CLEAR THR VARIABLE

  select @intNumber = 0

  select @vchNumber = ''

  select @vchConcepts = CONCEPT

  FROM#tmpTable

  WHEREIdTmpTable = @intCounter

  BEGIN TRY

  select @intNumber = left(@vchConcepts,1)

  END TRY

  BEGIN CATCH

  select @vchNumber = left(@vchConcepts,1)

  END CATCH

  IF @intNumber = 0

  BEGIN

  SELECT @vchNumber, @intID AS ID, @vchConcepts, @intIdNewConcept

  -- INSERT THE DATA IN YOUR PRODUCTION TABLE

  INSERT INTO #tmpProduction(idConcept,Concept )

  SELECT@intIdNewConcept, @vchConcepts

  END

  ELSE

  BEGIN

  SELECT@intNumber

  SELECT@intID = @intNumber

  SELECT@intIdNewConcept= CONCEPT

  FROM#tmpTable

  WHEREIdTmpTable = @intCounter

  END

  SELECT @intCounter = @intCounter + 1

  END

  SELECT * FROM #tmpProduction

  DROP TABLE #tmpProduction

  DROP TABLE #tmpTable