Seems to work reasonably well even for 2 dimensional splits. Using the general approach presented below, I have experimented with much wider parameters and increased number of rows. ...

Not yet Jeff, it's just that I'm so used to doing tally table/cross apply splits that I thought I'd try something different for a change!;-)

In fact this particular example can be addressed by a combination of PARSENAME and string manipulation to resolve the column list

DECLARE @ReplacePipe VARCHAR(MAX)

SET @ReplacePipe = REPLACE('1,a,b,20080101|2,c,d,20080102|3,e,f,20080102', '|', '.')

;WITH cte (SplitColumn)...

The reason this script is not working is because of a conflict in collation sequences. You are creating columns in your table #ProductTest with collation Latin1_General_CI_AS but this...

This sort of approach may be of interest to you. For testing purposes, I created a comma-separated file called Challenge.csv based on your Excel sheet CSV_File_Format. The next...

Using OPENROWSET BULK you can specify the column list eg

INSERT Price1

([TimePoint],

[rtPrice],

[daPrice],

[versifyId],

[externalNodeId]

)

SELECT

TimesPoint,

rtPrice,

daPrice,

versifyId,

externalNodeID

FROM OPENROWSET (BULK 'C:\Price.csv', FORMATFILE = 'C:\PriceFormat.txt') AS Z

I think you're just missing the SET FMTONLY OFF setting

SELECT a.* INTO #hdr FROM OPENROWSET('SQLNCLI','Server=(local)\SQLEXPRESS;Trusted_Connection=yes',

'EXEC(''SET FMTONLY OFF RESTORE HEADERONLY FROM DISK =''''c:\code3Billing\ClientSQLData\Master\code3billing.bak '''''')') AS a

If you embed your RESTORE FILELISTONLY in an EXEC statement, you can still use your OPENROWSET approach

SELECT a.* INTO #tmp FROM OPENROWSET('SQLNCLI','Server=(local)\SQLEXPRESS;Trusted_Connection=yes',

'EXEC(''SET FMTONLY OFF RESTORE FILELISTONLY FROM DISK=''''C:\code3Billing\ClientSQLData\Master\code3billing.bak'''''')') AS...

And another variation, this time using PIVOT

IF NOT OBJECT_ID('tempdb.dbo.#t', 'U') IS NULL DROP TABLE #t

SELECT 1 AS id, 'fname1' AS name INTO #t

UNION ALL SELECT 2, 'fname2'

UNION ALL SELECT 5,...

An alternative approach is to initially access the file using OPENROWSET BULK. The file is then split vertically using CROSS APPLY/Tally split method and within the same query...

Thanks Dave, yes it does make things a lot tidier

This variation is independent of the ANSI_NULLS setting. I use the same table variable @t_temp kindly supplied by bteraberry

;WITH cte AS

(

SELECT T.ID, Z.Col FROM @t_temp AS T...

OPENROWSET should do the trick eg

INSERT OPENROWSET('SQLNCLI', 'SERVER=REMOTE_SERVER;TRUSTED_CONNECTION=YES', 'SELECT name, num, email, ssn from Remote_Database.dbo.remote_table')

SELECT name, num, email, ssn FROM local_table

You can put your multiple statements in a stored procedure and then call the fully qualified stored procedure from your OPENROWSET statement.

SELECT * FROM OPENROWSET('SQLNCLI','Server=TheServer;Trusted_Connection=yes;',

'EXEC Databasename.dbo.Storedprocname')

You can also tackle this problem using OPENROWSET BULK.

The data is first referenced using OPENROWSET BULK via the CTE cteFileData. This uses the unstructured format file DataSampleBulk.fmt which...