Post reply

Getting problem while using getDate() to insert date in the Bulk insert

  • December 19, 2005 at 11:21 pm

    #364482

    Iam getting error like

    Bulk insert data conversion error (type mismatch)

    While inserting a date with getdate() in the file specified for the bulkinsert to insert a currentdate in the datetime col in the table.

  • December 19, 2005 at 11:52 pm

    #610514

    Can u specify that bulk insert query n both table structure...

    Regards,

    Papillon

  • December 20, 2005 at 12:37 am

    #610518

    CREATE TABLE table1 (

    Field1 varchar(255),

    Field2 datetime

    )

    and bulk insert query

    BULK INSERT table1 FROM '<path>\xyz.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t')

    and content of xyz.sql

    aa<Tab>getDate()    

     

  • December 20, 2005 at 1:50 am

    #610523

    That would be because SQL's trying to insert the string value 'getDate()' into a datetime field. Bulk insert just puts data into a table. It won't check to see if it's actually a function specified in the incoming data.

    To do what you're trying, add a default of GetDate() of Field2 in table table1 and don't specify that field in your bulk insert (you may need a format file for that).

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 20, 2005 at 11:11 am

    #610672

    How about something like:

    CREATE TABLE table1 (

    Field1 varchar(255),

    Field2 datetime DEFAULT (GETDATE())

    )

    remove the GETDATE() from your xyz.txt file and run the bulk insert query

    BULK INSERT table1 FROM '\xyz.txt' WITH (ROWTERMINATOR = '\n')

    -SQLBill

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

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