Post reply

BCP OR BULK INSERT

  • January 28, 2004 at 1:34 pm

    #107608

    I have a file TRIN20040127.GHT

    File contains

    1111-2222-3333,car                          ,           3,OK

    2222-222-2222,milk                         ,          78,OK

    3333-333-33333,water              ,          21,OK

    4444-444-44444,food              ,          23,OK

    I need to insert these data into a table by using BCP or BULK INSERT with in a stored procedure, the table structure is

    CREATE TABLE [Test] (

     [MyID] [int] IDENTITY (1, 1) NOT NULL ,

     [timestamp] [datetime] NULL ,

     [NUM] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [DESC] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [Qty] [smallint] NULL

    ) ON [PRIMARY]

    GO

    I need to ignore the MyID and timestamp columns when i insert the data from file.

    I tried BCP and BULK INSERT but got the following error messages.

    For BCP

    Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

    FOR BULK INSERT

    Server: Msg 4864, Level 16, State 1, Line 1

    Bulk insert data conversion error (type mismatch) for row 1, column 2 (timestamp).

    Any help would be appreciated

    Thanks in advance,

    srgangu

  • January 28, 2004 at 1:57 pm

    #492069

    Is bcp or Bulk Insert a requirement? This looks like precisely the sort of task that DTS is perfect for.


    R David Francis

  • January 28, 2004 at 3:12 pm

    #492078

    -- After table creation...

    Create View TestVW as

     Select NUM, [DESC], QTY

     From [Test]

    GO

    Bulk Insert TestVW from 'C:\temp\test.dat'

    With ( CODEPAGE = 'RAW',

     DATAFILETYPE = 'char',

     ROWTERMINATOR = ',OK\n',

     FIELDTERMINATOR = ',',

     LASTROW = 0,

     KILOBYTES_PER_BATCH = 1024)

    Drop View TestVW



    Once you understand the BITs, all the pieces come together

  • January 28, 2004 at 3:51 pm

    #492088

    Thanks for your responses,

    rdfozz,

    Nice to do bcp or bulk insert because i want to do with in stored procedure.

     

    ThomasH,

    I tried your solution but got the following error message.

    Server: Msg 4864, Level 16, State 1, Line 1

    Bulk insert data conversion error (type mismatch) for row 15, column 3 (Qty).

     

  • January 28, 2004 at 4:02 pm

    #492094

    Check out row 15 (or 14,16 )  of your raw data for maybe a missing comma, or invalid QTY value.

    I just cut & pasted your post data into a file and ran it.

    Also, may want to look in BOL for MAXERRORS parameter of Bulk Insert if your data may not be perfect.

     



    Once you understand the BITs, all the pieces come together

  • January 29, 2004 at 9:32 am

    #492189

    Hi,

    I am using DTS and working with no errors but inserting just first row, any ideas please, where i am doing wrong.

    Thanks.

  • January 29, 2004 at 9:36 am

    #492190

    Maybe you don't have your file properties set correctly - make sure you see multiple rows in the preview.


    R David Francis

  • January 29, 2004 at 9:38 am

    #492191

    Can you post, or Private Message me, the 1st few records of your file? May 1st 100 lines or so.

    Make sure what you post produces a error/problem on your end first.



    Once you understand the BITs, all the pieces come together

  • January 29, 2004 at 9:39 am

    #492192

    David,

    Thanks for quick response.

    When i test it,i see all rows but when run the job inserting just one.

Viewing 9 posts - 1 through 8 (of 8 total)

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