Ed Wagner (8/8/2013)
I'm not sure about using bcp, but here's how I would do it in a procedure.
BULK INSERT incoming_data
WITH (DATAFILETYPE = 'CHAR',
FIRSTROW = 2,
FORMATFILE = 'L:\format.xml',
MAXERRORS = 0);
You should be able to use this approach in a stored procedure to load your data from a database job or as needed.
Thanks for your help and patience Ed...still getting the same error...using the same code...
Msg 4823, Level 16, State 1, Line 1
Cannot bulk load. Invalid column number in the format file "L:\test.Fmt".
If i dint mention my scenario clearly...Here's the background information...this log file is generated from Perl and its a null separated file....When i contacted the application to use the comma,or tab separated file they replied back that there are some values with commas and rows with tab spaces in the log file....so they opted for null separated file...
i'm able to create a non-xml format file but when i create an xml format file its throwing the below error
bcp TempImport.dbo.cp_TempImport format nul -c -x -f test.xml -T
Error : Invalid Ordinal for field 2 in xml format file
And when i try to bulk copy the text file using non-xml format file....it throws me the following error
bcp TempImport.dbo.cp_TempImport in L:\test.log' -f L:\test.fmt -T
Error : Incorrect host-column number found in BCP format file
Also i have checked the ordinal number for table columns and it matches with format file....
Could you or anyone please help me on this and let me know if you have any questions
I really appreciate your help....
Thanks in advance