October 20, 2009 at 1:51 pm
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!
October 20, 2009 at 1:55 pm
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
October 20, 2009 at 10:37 pm
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
Change is inevitable... Change for the better is not.
October 20, 2009 at 10:54 pm
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.
October 21, 2009 at 12:21 am
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
Change is inevitable... Change for the better is not.
October 21, 2009 at 5:53 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply