When using the BULK INSERT command in SQL Server 2008 it returns:
(0 row(s) affected)
I am using this command to carry out the bulk insert:
BULK INSERT Test
FROM 'C:\DataFiles\Tests.dat'
WITH (FORMATFILE = 'C:\DataFiles\FormatFiles\TestFormat.Fmt');
GO
Tests.dat contains:
b00d23fe-580e-42dc-abd4-e8a054395126,48dd5dd6e3a144f7a817f234dd51469c,452eb8ce-6ae2-4e7a-a389-1097882c83ab,,, ,,,,Aria,,,160,,,86400,,2004-04-03 23:23:00.000,,2012-07-06 13:26:31.633,2012-07-06 13:27:44.650,3,,,,51B7A831-4731-4E2E-ACEC-06636ADC7AD3,,0,,0,,Field Name 1,,Field Name 2,,Field Name 3,,Field Name 4,
and the format file TestFormat.fmt contains:
9.0
39
1 SQLCHAR 0 37 "," 1 Key ""
2 SQLCHAR 0 37 "," 2 TestType ""
3 SQLCHAR 0 37 "," 3 CaseKey ""
4 SQLCHAR 0 30 "," 4 Height ""
5 SQLCHAR 0 30 "," 5 Weight ""
6 SQLCHAR 0 128 "," 6 PacemakerType Latin1_General_CI_AI
7 SQLCHAR 0 0 "," 7 Diary Latin1_General_CI_AI
8 SQLCHAR 0 0 "," 8 Indication Latin1_General_CI_AI
9 SQLCHAR 0 0 "," 9 Medication Latin1_General_CI_AI
10 SQLCHAR 0 37 "," 10 RecorderType ""
11 SQLCHAR 0 100 "," 11 RecorderSerial Latin1_General_CI_AI
12 SQLCHAR 0 0 "," 12 Comments Latin1_General_CI_AI
13 SQLCHAR 0 12 "," 13 Status ""
14 SQLCHAR 0 0 "," 14 AdditionalData Latin1_General_CI_AI
15 SQLCHAR 0 37 "," 15 OrderKey ""
16 SQLCHAR 0 12 "," 16 Duration ""
17 SQLCHAR 0 12 "," 17 Age ""
18 SQLCHAR 0 24 "," 18 RecordingStartDateTime ""
19 SQLCHAR 0 128 "," 19 Ward Latin1_General_CI_AI
20 SQLCHAR 0 24 "," 20 CreatedDateTime ""
21 SQLCHAR 0 24 "," 21 UpdatedDateTime ""
22 SQLCHAR 0 21 "," 22 UserGroupBits ""
23 SQLCHAR 0 24 "," 23 LastArchive ""
24 SQLCHAR 0 128 "," 24 PointOfCare Latin1_General_CI_AI
25 SQLCHAR 0 128 "," 25 Bed Latin1_General_CI_AI
26 SQLCHAR 0 37 "," 26 DownloadFacilityKey ""
27 SQLCHAR 0 37 "," 27 AnalysisFacilityKey ""
28 SQLCHAR 0 12 "," 28 Priority ""
29 SQLCHAR 0 37 "," 29 FacilityKey ""
30 SQLCHAR 0 12 "," 30 PacemakerTypeStandard ""
31 SQLCHAR 0 128 "," 31 TestTypeName Latin1_General_CI_AI
32 SQLCHAR 0 128 "," 32 UserDefined1Name Latin1_General_CI_AI
33 SQLCHAR 0 128 "," 33 UserDefined1Value Latin1_General_CI_AI
34 SQLCHAR 0 128 "," 34 UserDefined2Name Latin1_General_CI_AI
35 SQLCHAR 0 128 "," 35 UserDefined2Value Latin1_General_CI_AI
36 SQLCHAR 0 128 "," 36 UserDefined3Name Latin1_General_CI_AI
37 SQLCHAR 0 128 "," 37 UserDefined3Value Latin1_General_CI_AI
38 SQLCHAR 0 128 "," 38 UserDefined4Name Latin1_General_CI_AI
39 SQLCHAR 0 128 "\r\n" 39 UserDefined4Value Latin1_General_CI_AI
I cannot figure out why this isn't working. Other people have had similar problems because they had more fields than actual columns in their database. Or using .csv files which are not supported apparently.
This works fine on every other table in the database I am importing with no errors so I can't understand why it doesn't work here.
Best Answer
Finally found a solution to this. Turns out that it won't throw an error if there is a standard string in a field that should have a GUID. It could be because the said string was contained in colons.