Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Other Questions about BULK_INSERT Expand / Collapse
Author
Message
Posted Friday, March 20, 2009 11:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Sunday, March 22, 2009 5:14 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
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.


How to ask for help .. Read Best Practices here.
Post #681166
Posted Monday, March 23, 2009 6:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Monday, March 23, 2009 11:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
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.


How to ask for help .. Read Best Practices here.
Post #681667
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse