I have a sequential file that has header records and below it its detail
records. When I hit a new header know I know that that row-1 is the details
for the previous header (FYI: Header ID is ALWAYS unique)



i.e.



RowType        ID

Header           1

Detail             200

Detail             201

Detail             202

Detail             203

Header           2

Detail             200

Detail             201

Detail             202

Detail             203

Header           3

Detail             200

Detail             201

Detail             202

Detail             203



So what I want to do it give a parentID (Header ID) to the Detail rows as to
look like this.



RowType        ID        ParentID

Header           1                      1

Detail             200                  1

Detail             201                  1

Detail             202                  1

Detail             203                  1

Header           2                      2

Detail             200                  2

Detail             201                  2

Detail             202                  2

Detail             203                  2

Header           3                      3

Detail             200                  3

Detail             201                  3

Detail             202                  3

Detail             203                  3



Would there a simple SQL statement that could do this? Without using a
cursor if possible?



Thanks

Obviously you'll have to load the file to a table first. Tables have no
logical order of course. That means you'll have to preserve the order as a
row number or some other column. Without that information you can't relate
the header and detail using a query, a cursor or any other method (not
reliably anyway).

So your staging table should look something like this when you've loaded the
data:

CREATE TABLE staging (line_num INTEGER PRIMARY KEY, rec_type CHAR(6) NOT
NULL, id INTEGER NOT NULL);

INSERT INTO staging (line_num, rec_type, id)
SELECT 1, 'Header', 1 UNION ALL
SELECT 2, 'Detail', 200 UNION ALL
SELECT 3, 'Detail', 201 UNION ALL
SELECT 4, 'Detail', 202 UNION ALL
SELECT 5, 'Detail', 203 UNION ALL
SELECT 6, 'Header', 2 UNION ALL
SELECT 7, 'Detail', 200 UNION ALL
SELECT 8, 'Detail', 201 UNION ALL
SELECT 9, 'Detail', 202 UNION ALL
SELECT 10, 'Detail', 203 UNION ALL
SELECT 11, 'Header', 3 UNION ALL
SELECT 12, 'Detail', 200 UNION ALL
SELECT 13, 'Detail', 201 UNION ALL
SELECT 14, 'Detail', 202 UNION ALL
SELECT 15, 'Detail', 203 ;

Now you can use the following query:

SELECT D.rec_type, D.id, MAX(H.id) AS parent_id
FROM staging AS D
JOIN staging AS H
  ON D.line_num >= H.line_num
   AND H.rec_type = 'Header'
GROUP BY D.line_num, D.rec_type, D.id ;

Result:

rec_type id          parent_id
-------- ----------- -----------
Header   1           1
Detail   200         1
Detail   201         1
Detail   202         1
Detail   203         1
Header   2           2
Detail   200         2
Detail   201         2
Detail   202         2
Detail   203         2
Header   3           3
Detail   200         3
Detail   201         3
Detail   202         3
Detail   203         3

(15 row(s) affected)

It seems unlikely that the transformation phase should end there. Header and
Detail are presumably different entities that truly belong in different
tables.

Hope this helps.

My approach is essentially similar to David's.  I use this approach in
production quite a bit, so I use views to simplify things.  When I
import the data from the flat file (where sequence matters) I used
IDENTITY where David assigned the sequence manually in the INSERTs.

--The table layout has an IDENTITY column in addition
--to whatever columns are in your input data.
CREATE TABLE dbo.Whatever_Imported
(Seq int NOT NULL IDENTITY,
RowType char(6) not null,
RowID int not null)

--Load all the columns except the IDENTITY.
--Preserve order of file during the load.
--For this example I am using INSERT, but
--I always end up using DTS to load from a file.
INSERT Whatever_Imported (RowType, RowID) VALUES ('Header', 1)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 200)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 201)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 202)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 203)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Header', 2)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 200)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 201)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 202)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 203)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Header', 3)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 200)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 201)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 202)
INSERT Whatever_Imported (RowType, RowID) VALUES ('Detail', 203)
GO

--See the data as loaded
SELECT *
  FROM Whatever_Imported
GO

--A view to isolate the Header rows
CREATE VIEW Whatever_Imported_Headers
AS
SELECT *
  FROM Whatever_Imported
WHERE RowType = 'Header'
GO

--A view to add the Header information to the
--detail rows
CREATE VIEW Whatever_Imported_Detail
AS
SELECT Header_ID =
       (select MAX(RowID)
          from Whatever_Imported_Headers as H
         where H.Seq < D.Seq),
       Detail_ID = D.RowID
  FROM Whatever_Imported as D
WHERE D.RowType = 'Detail'
GO

--See the Header and Detail combined
select *
  from Whatever_Imported_Detail

Roy Harvey
Beacon Falls, CT


Show quoteHide quote

Help with a SQL Join statement
Check Constraint
More on querying remote server
many small queries vs one large query
Plz help me! I want to use SQL Server2000 DTS Import/Export Wizard functionality from C# application