Professional Web Applications Themes

Bulk Insert - Microsoft SQL / MS SQL Server

I want to Load a text file in a three column Table with three fields, all comma separated like:- 923335100025,Amer,Baig 923335100017,Asif,Qazi 923335100089,Maz 923335120084,Tahir,Ch 923335120085,Waseem My format file is 7.0 3 1 SQLCHAR 0 20 "," 1 msisdn 2 SQLCHAR 0 20 "," 2 name 3 SQLCHAR 0 20 "\r\n" 3 lname The problem is that the text file may contain all fields or not. How to load such file using bulk Insert. I have struggled a lot please help Regards, Amer...

  1. #1

    Default Bulk Insert

    I want to Load a text file in a three column Table with
    three fields, all comma separated like:-
    923335100025,Amer,Baig
    923335100017,Asif,Qazi
    923335100089,Maz
    923335120084,Tahir,Ch
    923335120085,Waseem

    My format file is
    7.0
    3
    1 SQLCHAR 0 20 "," 1 msisdn
    2 SQLCHAR 0 20 "," 2 name
    3 SQLCHAR 0 20 "\r\n" 3 lname

    The problem is that the text file may contain all fields
    or not.


    How to load such file using bulk Insert. I have struggled
    a lot please help


    Regards,

    Amer
    Mirza Guest

  2. #2

    Default Re: Bulk Insert

    Hi,
    You cannot omit a column in the data file.
    Instead put a comma whereever the third column is missing.

    Example
     [/ref]

    - Raja
     
    holding table as a 
    final destination 
    message [/ref]
    struggled 
    >
    >
    >.
    >[/ref]
    raja Guest

  3. #3

    Default Re: Bulk Insert

    This is not the required solution. There would be a way
    which will be used to fill trailing columns as null. I am
    unable to find that.

    Regards,

    Amer
     
    holding table as a 
    final destination 
    message [/ref]
    fields [/ref]
    struggled 
    >
    >
    >.
    >[/ref]
    Amer Guest

  4. #4

    Default Bulk Insert

    Hi,
    I having a remote data file. I want use it in the bulk insert.
    how i can specify the path.

    Babz


    LIN Guest

  5. #5

    Default Re: Bulk Insert

    Also, I don't want to map the path of the remote file. please advice.

    LIN


    LIN Guest

  6. #6

    Default Re: Bulk Insert

    What about using UNC?

    \\Server\Share\Filename

    from BOL: mk:MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsqlref.chm::/ts_ba-bz_4fec.htm


    'data_file'
    Is the full path of the data file that contains data to copy into the specified table or view. BULK INSERT can copy data from a disk (including network, floppy disk, hard disk, and so on).
    data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name.



    --

    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    www.SQLDTS.com
    I support PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org
    Allan Guest

Similar Threads

  1. Bulk eMailing
    By Whizzzper in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: December 4th, 09:23 AM
  2. BULK INSERT
    By sampsas23 in forum Coldfusion - Getting Started
    Replies: 10
    Last Post: January 12th, 01:50 PM
  3. BULK INSERT exercises from exam 70-229 training kit don't work
    By gene in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 14th, 12:00 PM
  4. Eliminate logging while doing Bulk Insert
    By RS in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 10th, 03:12 PM
  5. format file and bcp/bulk insert
    By Robert Taylor in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 7th, 11:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139