Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005
    Posts
    25

    Unanswered: Run Bulk insert in a cursor?

    Hello

    Can somebody help me out here ...
    I have 717 CSV-files that I need to import one by one, because in some files there may be EOF errors so I dont want to merge all files into one giant file and import, because I will an error that will make the import to fail.
    So I made a cursor that loops through a table with as many records that I have files.

    Here is the cursor

    Code:
    SET QUOTED_IDENTIFIER ON 
    declare @sql varchar(8000) 
    DECLARE @id varchar(50) 
    
    
    DECLARE db_cursor CURSOR FOR 
    SELECT  id 
    FROM  cnt 
    
    
    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @id  
    
    WHILE @@FETCH_STATUS = 0  
    BEGIN 
    
    
     --print 'c:\t1'+convert(varchar,@id)+'.txt'
     set @sql = 
    'BULK
    INSERT imp1   
    FROM "c:\inetpub\wwwroot\import2\import2\t1'+@id+'.csv"
    WITH 
    (
    FIELDTERMINATOR = ",",
    ROWTERMINATOR = "\n",
    TABLOCK
    )'
    
    
    set @sql = replace(@sql,'"','''')
    exec @sql
    --update cnt set upd = 1
    
    FETCH NEXT FROM db_cursor INTO @id  
    END  
    
    CLOSE db_cursor  
    DEALLOCATE db_cursor
    I get this error when running the cursor :
    Code:
    Msg 2812, Level 16, State 62, Line 32
    Could not find stored procedure 'BULK
    INSERT imp1   
    FROM 'c:\inetpub\wwwroot\import2\import2\t11.csv'
    WITH 
    (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK
    )'.
    But if I change the exec to a print I can execute the output without problems.
    I have also tried this without making a dynamic sql string but it seems like BULK INSERT cannot take a variable in its FROM clause.

    Anyone have a idea what is wrong and how I can fix this?

  2. #2
    Join Date
    Jul 2005
    Posts
    25
    I solved it
    I missed the parenthesis around the sql string i exec

Posting Permissions

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