March 20, 2009 at 11:25 am
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
March 22, 2009 at 5:14 pm
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[/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]
March 23, 2009 at 6:05 am
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
March 23, 2009 at 11:18 am
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[/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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply