Jeff Moden (9/20/2010)

---

steve-893342 (9/20/2010)

---

Jeff Moden (9/19/2010)

---

Yowch... I just tried XML-3 in a 1000 row by 1000 element test rig... not good. I stopped the run after two hours. ...

Jeff Moden (9/19/2010)

---

Yowch... I just tried XML-3 in a 1000 row by 1000 element test rig... not good. I stopped the run after two hours. Needless to say,...

Jeff Moden (9/19/2010)[hr

Hi Steve. Thanks for your involvement on this thread. Any chance of your running the code and returning the results for some extra data points for...

A CROSS APPLY variation. Thanks Cold Coffee for the @PhoneNumbers:-)

SELECT COUNT(Z.TEL)

FROM @PhoneNumbers

CROSS APPLY

(VALUES

(TEL1),

(TEL2),

(TEL3)

) AS Z (TEL)

Paul White NZ (9/19/2010)

---

I think...

Paul White NZ (9/19/2010)

---

On my machine, these are the results:

Tally:

Table 'Tally'. Scan count 10000, logical reads 30000, physical reads 0

Table 'CsvTest'. Scan count 1, logical reads 774, physical reads 0

CPU...

And thanks for the feedback, much appreciated:-)

Can't you just add an INSERT statement to your process? Something like

INSERT [MTHLY_XREF]

(

[NAME]

,[REL]

,[ESTNO]

,[DTFILD]

,[PUB]

,[TYPEOFDOCUMENT]

,[BTYP]

,[BKNO]

,[PGNO]

,[DISP]

,[DISPDT]

)

SELECT

SUBSTRING(BulkColumn, 3, 31) AS NAME

,SUBSTRING(BulkColumn,37, 6) AS REL

,SUBSTRING(BulkColumn, 46, 8) AS ESTNO

,SUBSTRING(BulkColumn, 57, 6) AS DTFILD

,SUBSTRING(BulkColumn, 68,...

One approach you could try is to first BULK INSERT the file into a single column staging table. Then using the SUBSTRING function you can resolve each individual column, cleaning...

simflex-897410 (9/13/2010)

---

Here is sample data.

IDNAMERELESTATE NODATE FILLEDPUBTYPE OF DOCUMENTBOOK TYPEBOOK NOPAGE NODISPOSITIONDISP DATE

1NULLNULL       090903  N  PETITION TO TERM GDNSHP MINOR   MIN   2962146GRANTED              90903

2ALBA, LOUIS R                   DEC   147112  080191  Y  APPN YEAR'S...

Not sure, I almost invariably bring my splitter code...

I think there are several things wrong here. Firstly you won't be able to SELECT or ORDER BY ContractID in your final SELECT statement because ContractID is consumed in the...

WayneS (9/13/2010)

---

Test code:

SET NOCOUNT ON;

print replicate('*',30);

Print 'Jeff''s function';

print replicate('*',30);

SET STATISTICS IO,TIME ON;

select

count(*)

from

CsvTest

cross apply

dbo.Split8KX(CsvParameter, ',');

SET STATISTICS IO,TIME OFF;

print replicate('*',30);

print 'Inline table valued function';

print replicate('*',30);

SET STATISTICS...

Peter Brinkhaus (9/13/2010)

---

I am not an XML Ninja either, but you can try a inline table valued function

set statistics io on

set statistics time on

print 'Inline table valued function'

select

count(*)

from

...

shell_l_d (9/10/2010)

---

Wondering if there's a way to do...