Other Questions about BULK

SQLServerCentral is supported by Red Gate Software Ltd.

Other Questions about BULK_INSERT

Rate Topic

Display Mode

Topic Options

Author

Message

pedro.ribeiro

Posted Friday, March 20, 2009 11:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982

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

Post #680602

Mohit K. Gupta

Posted Sunday, March 22, 2009 5:14 PM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 10, 2013 10:58 AM
Points: 941, Visits: 1,042

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.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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

How to ask for help .. Read Best Practices here.

Post #681166

pedro.ribeiro

Posted Monday, March 23, 2009 6:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982

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

Post #681344

Mohit K. Gupta

Posted Monday, March 23, 2009 11:18 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 10, 2013 10:58 AM
Points: 941, Visits: 1,042

When executing dynamic SQL, you need to use

EXEC (@SQLStatement)

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

Thanks.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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

How to ask for help .. Read Best Practices here.

Post #681667

« Prev Topic | Next Topic »

Permissions