Other Questions about BULK_INSERT

  • Hi masters,

    Can you please help me understanding this bulk questions? i'm implementing bulk, and this information is very important for me.

    Thank you in advance.

    Question 1)

    If i have a stored procedure, that as this two BULK Insert Statements:

    create proc test

    as

    set nocount on

    truncate table bulk_table1

    BULK INSERT bulk_table1

    FROM '..................1.txt'

    WITH

    (

    FIRSTROW =2,

    FIELDTERMINATOR ='|',

    ROWTERMINATOR ='',

    tablock,

    DATAFILETYPE ='widechar'

    )

    truncate table bulk_table2

    BULK INSERT bulk_table2

    FROM '.....................2.txt'

    WITH

    (

    FIRSTROW =2,

    FIELDTERMINATOR ='|',

    ROWTERMINATOR ='',

    tablock,

    DATAFILETYPE ='widechar'

    )

    set nocount off

    go

    If the first bulk statement fails (because of errors) will the second one, be executed and commited if it as no errors?

    I what that only if the two bulk statements execute successfully, they can be commited to the database.

    I don't know if it's allright as it is right now (in the above proc), or if i have to put in the beginning of the procedure the expression "Begin Transaction" and on the end of the procedure, the expressions "IF @@ERROR <>0 Commit Transaction"

    Question 2)

    if i do not especify the option "MAXERRORS " on the Bulk statement, i have been reading that the max errors permited by the Bulk Insert are 10. But does this mean that if i have for instance 8 errors, during the bulk from the file to the database will the command be successfully executed and commited to the database?

    Suppose the proc above (question1) . If the first bulk raises 8 errors , will the two BULK commands be executed successfully?

    Question 3)

    Suppose the procedure below:

    create proc test (@path1)

    as

    set nocount on

    truncate table bulk_table1

    BULK INSERT bulk_table1

    FROM @path1 + '\1.txt'

    WITH

    (

    FIRSTROW =2,

    FIELDTERMINATOR ='|',

    ROWTERMINATOR ='',

    tablock,

    DATAFILETYPE ='widechar'

    )

    truncate table bulk_table2

    BULK INSERT bulk_table2

    FROM '@path1 + '\2.txt'

    WITH

    (

    FIRSTROW =2,

    FIELDTERMINATOR ='|',

    ROWTERMINATOR ='',

    tablock,

    DATAFILETYPE ='widechar'

    )

    set nocount off

    go

    This dont work because of the variable @path1.

    I need to receive part of the path from ho is calling the procedure. how can i code the procedure, so that it works well with a variable path?

    tks again,

    Pedro

  • 1) Yes if first one fails second one will execute. If you begin transaction when there is an error it rolls back so your transaction cannot wrap against both bulk inserts.

    2) Right it will not fail but the @@error value changes so you can use that to see if you should continue with your process after the bulk process failed or was successful.

    3) You dynamic SQL ...

    SET @SQLStatement = 'BULK INSERT bulk_table2 FROM ''' + @path1 + '\2.txt''

    WITH

    (

    FIRSTROW =2,

    FIELDTERMINATOR =''|'',

    ROWTERMINATOR ='''',

    tablock,

    DATAFILETYPE =''widechar''

    )'

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Hi,

    I have tried to do the dinamyc SQL Statement, like this:

    Create proc BULK_INSERT_DATA(@PATH varchar(500))

    as

    declare @SQLStatement as varchar(200)

    set nocount on

    truncate table bulk_1

    SET @SQLStatement = 'BULK INSERT bulk_1

    FROM '''+@PATH+'\1.txt''

    WITH

    (

    FIRSTROW =2,

    FIELDTERMINATOR =''|'',

    ROWTERMINATOR ='''',

    tablock,

    DATAFILETYPE =''widechar''

    )'

    execute @SQLStatement

    set nocount off

    go

    BUT This raises an error:

    (Could not find stored procedure 'BULK INSERT bulk_Contribuintesest

    FROM 'C:\bulk\PastaExportacao SGCTCentral - 20090320 121402_CS\ContribuintesTipo21.txt'

    WITH

    (

    FIRSTROW =2,

    FIELDTERMINATOR ='|',

    '.)

    what is rong with this procedure? can you help please?

    In my case i have other BULK Inserts on the same procedure. how can i do it , so that it will all execute? can i do like this?

    Create proc BULK_INSERT_DATA(@PATH varchar(500))

    as

    declare @SQLStatement as varchar(200)

    set nocount on

    truncate table bulk_1

    SET @SQLStatement = 'BULK INSERT bulk_1

    FROM '''+@PATH+'\1.txt''

    WITH

    (

    FIRSTROW =2,

    FIELDTERMINATOR =''|'',

    ROWTERMINATOR ='''',

    tablock,

    DATAFILETYPE =''widechar''

    )'

    execute @SQLStatement

    truncate table bulk_2

    SET @SQLStatement = 'BULK INSERT bulk_2

    FROM '''+@PATH+'\2.txt''

    WITH

    (

    FIRSTROW =2,

    FIELDTERMINATOR =''|'',

    ROWTERMINATOR ='''',

    tablock,

    DATAFILETYPE =''widechar''

    )'

    execute @SQLStatement

    Tks,

    Pedro

    set nocount off

    go

  • When executing dynamic SQL, you need to use

    EXEC (@SQLStatement)

    Those brackets tell SQL Server it is not a store procedure call.

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

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

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