Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Question Unanswered: Bulk Insert not inserting despite query execing successfully

    I'm attempting to insert data from a CSV file into a table. The table was created with the following:

    CREATE TABLE [MyDB].[dbo].[MyTable] (
    [DEPT] varchar (8000) NULL,
    [SUPPLIER] varchar (8000) NULL,
    [RANGE] varchar (8000) NULL,
    [EAN] varchar (8000) NULL,
    [ITEM] varchar (8000) NULL,
    [PO DATE] varchar (8000) NULL,
    [PO] varchar (8000) NULL,
    [PAGE] varchar (8000) NULL,
    [CUSTOMER] varchar (8000) NULL,
    [ORDERED] varchar (8000) NULL,
    [DELIVERED] varchar (8000) NULL,
    [OUTSTANDING] varchar (8000) NULL,
    [SO DATE] varchar (8000) NULL,
    [SO] varchar (8000) NULL,
    [Col015] varchar (8000) NULL
    )

    I'm using the following bulk insert:

    BULK INSERT [MyDB].[dbo].[MyTable]
    FROM 'c:\data.csv' WITH (check_constraints, ROWTERMINATOR = '\n\r', CODEPAGE = 'OEM', DATAFILETYPE = 'char', FIELDTERMINATOR = ',',
    FIRSTROW = 2, KEEPNULLS)

    Here's a sample of the CSV data:

    BED FURN,DUCKERS,MANHATTAN COLLECTION,000000005238,MANHATTAN COLLECTION 5FT DENVER BEDSTEAD,12-Jan-01,9339,,,1.00,0.00,1.00,,0
    HARD FURN,GUSTO,STOVES,000000005381,STOVES CORDOBA GARDEN STOVE,11-Jan-01,9429,,,10.00,0.00,10.00,,0
    HARD FURN,GUSTO,STOVES,000000005382,STOVES SARAGARSA GARDEN STOVE,11-Jan-01,9429,,,10.00,0.00,10.00,,0
    HARD FURN,GUSTO,STOVES,000000005383,STOVES PREMIUM GARDEN STOVE,11-Jan-01,9429,,,10.00,0.00,10.00,,0
    HARD FURN,BLAKEMOR,LIGHTING,000000003732,LIGHTING L1436 BLAKEAMORE,03-Apr-01,9616,,,2.00,1.00,1.00,,0
    BED FURN,DUCKERS,DRAWERS,000000001541,DRAWERS PINE 2 DWR SET ANT,10-Apr-01,9678,,,2.00,0.00,2.00,,0

    It executes fine, but MyTable remains unchanged. I'm running SQL Server 2000 PE (w/SP3) on Windows XP.

    Any assistance would be greatly appreciated.

    Regards,

    Jim.

  2. #2
    Join Date
    Jul 2003
    Posts
    1

    Re: Bulk Insert not inserting despite query execing successfully

    Jim,

    the length of the columns exceeds the maximum allowed, I think it is about 16,000 bytes. I notice that all your columns are varchar(8000), is there any reason for this or could you make them smaller?

    An alternative method to the bulk insert would be to use DTS to import the data.

  3. #3
    Join Date
    Jul 2003
    Posts
    2
    Troy,

    Thanks for the reply. I've changed the table to the following:

    CREATE TABLE [CaseysLabels].[dbo].[po_labels]([DEPT] text NULL, [SUPPLIER] text NULL, [RANGE] text NULL,
    [EAN] text NULL, [ITEM] text NULL, [PO DATE] text NULL, [PO] text NULL, [PAGE] text NULL,
    [CUSTOMER] text NULL, [ORDERED] text NULL, [DELIVERED] text NULL, [OUTSTANDING] text NULL,
    [SO DATE] text NULL, [SO] text NULL, [Col015] text NULL)

    I also tried:

    CREATE TABLE [CaseysLabels].[dbo].[po_labels] (
    [DEPT] varchar (255) NULL,
    [SUPPLIER] varchar (255) NULL,
    [RANGE] varchar (255) NULL,
    [EAN] varchar (255) NULL,
    [ITEM] varchar (255) NULL,
    [PO DATE] varchar (255) NULL,
    [PO] varchar (255) NULL,
    [PAGE] varchar (255) NULL,
    [CUSTOMER] varchar (255) NULL,
    [ORDERED] varchar (255) NULL,
    [DELIVERED] varchar (255) NULL,
    [OUTSTANDING] varchar (255) NULL,
    [SO DATE] varchar (255) NULL,
    [SO] varchar (255) NULL,
    [Col015] varchar (255) NULL
    )

    And:

    CREATE TABLE [CaseysLabels].[dbo].[po_labels] (
    [DEPT] varchar (30) NULL,
    [SUPPLIER] varchar (30) NULL,
    [RANGE] varchar (30) NULL,
    [EAN] varchar (30) NULL,
    [ITEM] varchar (100) NULL,
    [PO DATE] varchar (30) NULL,
    [PO] varchar (30) NULL,
    [PAGE] varchar (30) NULL,
    [CUSTOMER] varchar (30) NULL,
    [ORDERED] varchar (30) NULL,
    [DELIVERED] varchar (30) NULL,
    [OUTSTANDING] varchar (30) NULL,
    [SO DATE] varchar (30) NULL,
    [SO] varchar (30) NULL,
    [Col015] varchar (30) NULL
    )

    However, it's still not importing the data. I actually got the original create table statement from DTS. I created a DTS package and analysed the queries in Profiler.

    The DTS package works, but I need to be able to perform the same operation with queries as I'm trying to write a piece of software that imports the CSV data automatically when the user double-clicks a CSV file.

    Jim.

Posting Permissions

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