Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Are you absolutely sure the line endings are \r\n? Should it maybe be ,\r\n because of the trailing comma? This is the most common issue I see where BULK INSERT doesn't pan out. I also don't see any real reason for you to use a format file instead of just ROWTERMINATOR/FIELDTERMINATOR. –  Aaron Bertrand Jul 18 '13 at 12:36
1  
Also, I have no idea what you mean by .csv files which are not supported apparently ... what makes you think .csv files are "not supported"? –  Aaron Bertrand Jul 18 '13 at 12:39
    
That trailing comma is just there because there is another field after it. I will try another command and use ,\r\n –  TechAUmNu Jul 18 '13 at 12:44
    
Still has (0 row(s) affected) –  TechAUmNu Jul 18 '13 at 12:47

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

ouch. reaching back into my memory - I remember that sometimes I had to put a return at the end of the format file. (SQL 7, maybe even 6.5?) So, maybe hit enter at the end of the format file, or optionally, make sure you didn't hit enter

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.