Yes that's right Jeff, you wouldn't need a format file for BULK INSERT. It really depends how much flexibility you need. OPENROWSET BULK allows you to...

Yes it should always work up to the first INSERT query viz

INSERT #ConnectivityTest

SELECT BulkColumn

FROM OPENROWSET (

BULK 'C:\ConnTest.txt',

FORMATFILE = 'C:\grasshopper2.fmt',

FIRSTROW = 1

) AS Z

--WHERE NOT BulkColumn =...

Sorry folks, I misunderstood where the text file actually started!

Maybe this one will work for you

You will need to create a format file (grasshopper2.fmt) as follows

7.0

1

1SQLCHAR08000"\R\N"1BulkColumn

Again don't forget to replace...

What do you think?:laugh:

Jeff makes the point about BULK INSERT being quite restrictive because you have to match up the column list and you cannot manipulate the individual columns very easily. OPENROWSET BULK...

Wow Mister Magoo, that ROW CONSTRUCTOR syntax is certainly a lot more friendly than UNPIVOT. Seeing your script, I just had to create the UNPIVOT equivalent which is a bit...

CROSS APPLY Variation

Do you think this might be a contender?

WITH cte1 AS

(

SELECT

1 AS A1 UNION ALL SELECT 1

)

,

cte2 AS

(

SELECT A1 FROM cte1 CROSS APPLY

(

SELECT A1 as row UNION ALL...

THE TRILLION Revisited

20 UNPIVOT/Million Row Base Table/Single Cross Join Variation

61 hours 58 minutes

Data from sys.dm_exec_query_stats (times are in microseconds)

total_elapsed_time223,100,308,594

total_worker_time ...

If you haven't tried this already Mister Magoo, it would be interesting if you were to create your base table using UNPIVOT rather than 32 UNION SELECT statements and see...

Whacky man!:crazy:

Is FIZZBUZZ becoming an art form?

Well congratulations indeed Mister Magoo on realizing THE TRILLION!

It's the theme of the day!

THE TRILLION Revisited - The 40 Unpivot Skyscraper

Well I feel compelled to push the multiple unpivots approach to extremes. 40 unpivots against a 2 column block yields over...

Taller and Narrower 20 Unpivot X 2 Column Block Query has the Edge

Well the answer to the above (SQL script enclosed) is absolutely yes. The 20 unpivot x...

So will this 20 unpivot variation one go even faster?

WITH cte1 AS

(

SELECT A FROM

(

SELECT

1 AS A1,1 AS A2,

1 AS B1,1 AS B2,

1 AS C1,1 AS C2,

1 AS...

Tall and Narrow Approach has the Edge

Well the tall and narrow query seems to work quite well. 10 unpivots X 4 column block is my fastest so far...