Post reply

Bulk Inser Format File Question

  • April 19, 2011 at 1:48 pm

    #389322

    Hello, I am using Bulk Insert as a datafeed loading tool for both Oracle and SQL Server. We use a format file for SQL Server and a control file for Oracle. In oracle I can specify a column default value directly in the control file like so(see region column definition):

    LOAD DATA

    INFILE *

    INTO TABLE modified_data

    ( rec_no "my_db_sequence.nextval",

    region CONSTANT '31',

    time_loaded "to_char(SYSDATE, 'HH24:MI')",

    data1 POSITION(1:5) ":data1/100",

    data2 POSITION(6:15) "upper(:data2)",

    data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"

    )

    Is there a way to do this directly in a SQL Server format file? I cannot set the default at the table level.

    Thank you!

    Best Regards,

    ~David

  • April 20, 2011 at 4:12 pm

    #1314253

    You can do it using OPENROWSET BULK instead of BULK INSERT and still use the same format file you already created. Here is a proof of concept that works on SQL 2008 Express Edition.

    Create file C:\@\MyTestEmptyField2-c.Dat with this in it:

    1,,2

    3,c,4

    Create file C:\@\MyTestDefaultCol2-f-c.Fmt with this in it:

    10.0

    3

    1 SQLCHAR 0 7 "," 1 Col1 ""

    2 SQLCHAR 0 100 "," 2 Col2 SQL_Latin1_General_CP1_CI_AS

    3 SQLCHAR 0 100 "\r" 3 Col3 SQL_Latin1_General_CP1_CI_AS

    Then you can use the ISNULL function in your SELECT column list like this to default specific values:

    SELECT Col1,

    ISNULL(Col2, 'hello!'),

    Col3

    FROM OPENROWSET(BULK 'C:\@\MyTestEmptyField2-c.Dat',

    FORMATFILE= 'C:\@\MyTestDefaultCol2-f-c.Fmt') AS t1 ;

    GO

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 21, 2011 at 6:03 am

    #1314442

    Can i use this with INSERT INTO?

    Best Regards,

    ~David

  • April 21, 2011 at 7:41 am

    #1314509

    Sure, you can use it just like a table...with INSERT INTO, or even as a SELECT INTO.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 21, 2011 at 7:43 am

    #1314511

    Excellent. Thank you! 🙂

    Best Regards,

    ~David

  • April 21, 2011 at 7:49 am

    #1314519

    You're very welcome 🙂

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 6 posts - 1 through 5 (of 5 total)

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