September 18, 2008 at 11:55 am
I was able to create my wide table, no worries.
But I'm trying to import a tab delimited text file and it blows up every time. I used the wizard and bulk insert, and neither worked. Is there a trick to importing into sparse columns?
Here is my bulk insert statement ...
BULK INSERT CALL_REPORTS
FROM 'c:\data\callreports.txt'
WITH (
FIELDTERMINATOR='\t',
ROWTERMINATOR='\r'
)
I get this error ...
Msg 9421, Level 16, State 1, Line 1
XML parsing: line 1, character 6353, illegal name character
Thots, anyone?
Cheers,
Mo
September 18, 2008 at 12:27 pm
Can you attach the file?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 18, 2008 at 12:35 pm
Sure ... this includes the first ten rows of data.
September 18, 2008 at 2:11 pm
From 2008 BOL on BCP & Sparse columns:
Bulk Copy (BCP) Support for Sparse Columns
There are no changes to the BCP API in either ODBC or OLE DB for the sparse columns or column_set features.
If a table has a column_set, sparse columns are not handled as distinct columns. The values of all sparse columns are included in the value of the column_set, which is exported in the same way as an XML column; that is, as varbinary(max) if bound as a binary type, or as nvarchar(max) if bound as a char or wchar type). On import, the column_set value must conform to the schema of the column_set.
For queryout operations, there is no change to the way explicitly referenced columns are handled. column_set columns have the same behavior as XML columns and sparseness has no effect on the handling of named sparse columns.
However, if queryout is used for export and you reference sparse columns that are members of the sparse column set by name, you cannot perform a direct import into a similarly structured table. This is because BCP uses metadata consistent with a select * operation for the import and is unable to match column_set member columns with this metadata. To import column_set member columns individually, you must define a view on the table that references the desired column_set columns, and you must perform the import operation using the view.
I suspect that BULK INSERT has similar limitations to BCP and as such is looking for your Sparse column_set as a single XML value per row, which it tries to parse and finds an invalid character("&") in data that it is trying to parse into a name.
If my guess is right, then you will either need to format the input Sparse columns as XML or else use a View with explicit columns as the BULK INSERT target (which will also probably limit how many columns at a time you can input).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 18, 2008 at 2:13 pm
Your other choice would be to bring it into a staging with a single NVarchar(MAX) per row, and then parse it from within SQL into Sparse column_set XML.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply