How do I BULK INSERT only one or some of the fields in the row?

  • i tried to insert a single column from a txt file a sql DB say the file contains

    1 tab field2 tab field3

    2 .................

    3 ..........etc

    create table #aBSearchbTempcTableB

    (

    ItemID bigint null,

    UnUsedDataVARCHAR(50)null

    )

    BULK INSERT

    #aBSearchbTempcTableB

    from

    '\\ppdys1402\Share\ids.txt'

    WITH

    (

    FIELDTERMINATOR = '\t',

    ROWTERMINATOR = ''

    --FIRSTROW = 2

    )

    this helps me to do that .. here my delimeter is tab so if many fields are there i can easily import the 1st column into that serverDB .. But i have situation where the file can contain only ids

    for eg without tab

    ie

    1enter

    2..

    3..

    now here no tab delimiter is there .. now i need a script that can import a 1st column into serverDB even if the txt file contains one column or more that one column

  • You need to use a BCP format file. See Books Online for how to build one... it's not that hard.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank u .. and like to know is there any other option other than using a format file .. if so help me

  • Sure, you could write a VBS script, a smidgen of C# or bit of Java code, or any other number of options. ALL of them will be slower than BCP with a format file... sometimes, MUCH slower. A very well Java program will import 1.2 million rows 12 fields wide in only about 16 minutes with no validation to speak of. BCP will do the same thing, trap bad rows in a file, and do some other minor validations for length and the like in only 51 seconds.

    So, make the choice... it's your data... But I think you not spending the required 10-15 minutes to make the format file would be a mistake on your part... takes that long to write the Java... longer if you check it into source control.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, I have found that information helpful to a format file newbie is hard to come by. Maybe you know of an article that explains it a bit better than BOL?

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Greg Snidow (3/14/2008)


    Jeff, I have found that information helpful to a format file newbie is hard to come by. Maybe you know of an article that explains it a bit better than BOL?Greg

    BOL actually has one of the better explanations... but, if there's nothing private in it, attach a sample of your file and a record layout and lemme show you how it's done...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mukundbtech (3/13/2008)


    Thank u .. and like to know is there any other option other than using a format file .. if so help me

    Rethinking this, DTS would probably be the easiest for you... it won't be the fastest, but if might be the easiest.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 1enter

    2..

    3..

    now here no tab delimiter is there .. now i need a script that can import a 1st column into serverDB even if the txt file contains one column or more that one column

    If there is only one column in your text file then your field terminator can be anything that does not appear in the data (ie: , | tab). You just need to have your row terminator set correctly to either a line feed or carriage return or both. The bcp process will run into the row terminator before the field terminator and understand that there is only one column.


  • Jeff Moden (3/15/2008)


    mukundbtech (3/13/2008)


    Thank u .. and like to know is there any other option other than using a format file .. if so help me

    Rethinking this, DTS would probably be the easiest for you... it won't be the fastest, but if might be the easiest.

    And more specifically - the "import and Export data" wizard (which automates DTS). Assuming you're doing a "straight" import - it should be fairly performant (not quite as good as the command-line options, but still - pretty darn fast). If you're worried about data quality - go with the "create table" option on the destination side: once it's done bringing it in - then use T-sql to finish cleaning up the data and then push it into your production table.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • attached are my input files ... previously i used Bulkinsert method when our project requirement is to import only a single column file(my first attch) values .. then when they ask for multicolumn (my second attach)i implemented as i given in first comment . But now the requirement is to use both the single and multicolumn and now i have implemented in the clientside code to read the values and send to the SP.but i like to know other than using extra files like BCP format or client side code or DTS , is there we can implement in a single stretch like using bulkinsert .

  • You can bcp your whole file into a staging/temp table and then use an insert statement to append the specific fields you want into your main table. Not the most efficient method but meets your requirements of no format file and no DTS.


  • mukundbtech (3/20/2008)


    but i like to know other than using extra files like BCP format or client side code or DTS , is there we can implement in a single stretch like using bulkinsert .

    Yes there is... look up "sp_AddLinkedServer" in Books Online and refer to example "H. Use the Microsoft OLE DB Provider for Jet to access a text file"

    You can also take a look at OpenRowset...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (3/20/2008)


    Yes there is... look up "sp_AddLinkedServer" in Books Online and refer to example "H. Use the Microsoft OLE DB Provider for Jet to access a text file"

    You can also take a look at OpenRowset...

    Doesn't sp_AddLinkedServer require a schema.ini file for text files like a bcp format file?


  • Nope... here's the example from BOL... and, I've tested it... works fine... and despite what it says about schema.ini, you don't need it for comma delimited files.

    H. Use the Microsoft OLE DB Provider for Jet to access a text file

    This example creates a linked server for directly accessing text files, without

    linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0

    and the provider string is 'Text'.

    The data source is the full pathname of the directory that contains the text files.

    A schema.ini file, which describes the structure of the text files, must exist in the

    same directory as the text files. For more information about creating a schema.ini

    file, refer to Jet Database Engine documentation.

    --Create a linked server

    EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',

    'Microsoft.Jet.OLEDB.4.0',

    'c:\data\distqry',

    NULL,

    'Text'

    GO

    --Set up login mappings

    EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL

    GO

    --List the tables in the linked server

    EXEC sp_tables_ex txtsrv

    GO

    --Query one of the tables: file1#txt

    --using a 4-part name

    SELECT *

    FROM txtsrv...[file1#txt]

    The really cool thing is... it'll read "ragged right delimited files"... try it...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Maybe I'm being thick but doesn;t that say a schema.ini file must be in the same directory as the file being imported?


Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply