Ed Wagner (8/6/2013)
For repeated data loads from text files, I find it better to use a format file to control what goes where. It takes a bit of work to set up, but it also gives you a much finer level of control over what to include, terminator characters and what source data maps to what destination fields.Then all you have to do is include a FORMATFILE parameter in your BULK INSERT and it takes care of the rest. If you're interested, here's the MSDN article to get you started. http://msdn.microsoft.com/en-us/library/ms178129.aspx
Thanks Ed for the suggestion...i have tried it....using the below code....and got the following error..
format file :
bcp dbatest.dbo.test format nul -c -t, -f test.Fmt -T
BULK INSERT dbatest.dbo.test
FROM 'L:\test.log'
WITH (FIELDTERMINATOR = '\0',ROWTERMINATOR = '', formatfile = 'L:\test.Fmt')
error :
Msg 4823, Level 16, State 1, Line 1
Cannot bulk load. Invalid column number in the format file "L:\test.Fmt".
Please let me know your thoughts...thanks