Peter,

It's been a long day. I think I found a solution that will work for my XML sheets into existing tables.

keeping all your suggestions:

(1) - Comment section: -- Temp Table (lines: 297-301)

add: AND e.is_identity=0 AND e.system_type_id<>189;

(2) - Comment section: -- Physical Table (Lines: 305-309)

add: AND e.is_identity=0 AND e-system_type_id<>189;

In the receiving table we have 1 PK and 2 FKs so instead of adding the suggested line, I:

chg to: AND e.max_length < 0;

(3) - Section where RAISERROR reads ('Some non-nullable columns (Lines: 363-366)

add: DELETE #Columns WHERE ExcelColNum = 0;

Running the code:

In your SQL: ImportExcel.sql Line 314 is: IF @FirstRowIsHeader=1

Running the Sproc with these params:

EXEC dbo.[uspImportExcelSheet]

@ExcelFileName=N'C:\Conversion\Source\ETL\Feeds\SalesRowCnt10.xml',

@WorkSheetName=N'Sheet1',

@OutputTableName=N'dbo.Sales',

@FirstRowIsHeader=1,

@Debug=1

SELECT * FROM Sales

Yields:

A table full of NULLS

In my xml spreadsheet, the first row is the header row, So param: @FirstRowIsHeader must be 1.

Back to the SQL: Changing IF @FirstRowIsHeader=1 to IF @FirstRowIsHeader=0, running the same execute statements above, yields:

---YES--- Exactly what I was looking for. 10 rows/117 columns of xml spreadsheet data inserted into an existing table on the database without the header detail in the 1st row.

Testing with the 13K+ rows/117 columns of xml spreadsheet data yields: Nice! Works great!

I also tested it leaving @FirstRowIsHeader=1 in SQL and changing the param @FirstRowIsHeader=0, this resulted in the header row added as part of the data - not good.

Thanks!

JG