bulk insert issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

bulk insert issue

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-22 : 08:48:20

Hi there guys hope everyone is having a better day than I.
I started to run a procedure that uses a parameter to import 40 files from a given directory using the bulk insert into one table but if one of those files doesn't exsist in the given directory it just stops and doesn't carry through to the next file which I want it to do :

the code is as follows(excuse the cursor!):
ALTER PROCEDURE InsertItemsales
as

DECLARE @date as varchar(100)
DECLARE @sql as varchar(200)
DECLARE @storeno as smallint
DECLARE storecursor CURSOR FOR
Select store_no From
retail2 Where store_no in ( 208,189,111,181,270,204,206,182,185,201,112,110,272,209,103,108,279,109,278,104,203,186,202,180,281,271,276,273,207,280,183,117,211,114,190,113,105,210,274,118,205,184)

DROP TABLE dbo.tmpisaleimport
CREATE TABLE dbo.tmpisaleimport ([salesdata] [char] (56))
DROP TABLE dbo.itemsales_stores_import_error
CREATE TABLE [itemsales_stores_import_error] (
[StoreNumber] [smallint] NULL ,
[DateInserted] [datetime] NULL CONSTRAINT [DF__itemsales__DateI__43F17C88] DEFAULT (getdate())
) ON [PRIMARY]

OPEN storecursor
FETCH NEXT FROM storecursor INTO @storeno
WHILE @@FETCH_STATUS = 0
--get date number from datetoperiods_virtual table
BEGIN
select
@date = cast(datediff(day,min(date),getdate()) as varchar(3))
from
dbo.DatesToPeriods_Virtual
where
period = 1
and week = 1
and finyear in
(select
finyear
from
dbo.DatesToPeriods_Virtual
where
date > DATEADD(day, -1, getdate()))

select @date = right('000' + convert(varchar(10), @date), 3)
select @date =''''+ '\\jy2003smem012\Data\Iris6\Data\HOSTIN\'+ cast(@storeno as char(3))+'.'+@date+''''

-- bulk insert using @date parameter
Set @sql='
BULK INSERT [tmpisaleimport]
FROM '+@date
Exec(@sql)
If @@error <> 0
begin
-- log the store number that the bulk insert failed on
INSERT INTO itemsales_stores_import_error(@storeno)
VALUES (@storeno)
FETCH NEXT FROM storecursor INTO @storeno
end
FETCH NEXT FROM storecursor INTO @storeno
END
CLOSE storecursor
DEALLOCATE storecursor

Now in the red are all the store numbers but sometimes they do not all come through to the given directory(ie store file failures) which obviously fails the bulk insert in green my intention is to weed out these store numbers in orange and throw them into a error table but the whole thing stops working once the bulk insert fails for that given store number that is not there.

Does anyone please have any great ideas what could be a solution here please ?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-22 : 09:19:26

Have a look at this
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

It bulk inserts and archives all files in a folder.
It's not much effort to delete all the filenames from the temp table that you don't want (and probably change the bullk insert statement to your format) and that'll get round te problem of the files not being there.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-22 : 09:21:55

I don't think this is going to be easy to apply to my code Nigel after taking a second look, if there is no other option I will use DTS's instead but surely there is a solution to the orange highlighted text above that I can use to continue the Bulk insert if the store no does not exsist.Sorry to be picky here.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-22 : 10:47:54

quote:
and that'll get round te problem of the files not being there.

The problem is that I need to insert all storenumbers that were not included in the bulkinsert intothe itemsales_stores_import_error table so as to know which ones I need to follow up on.
thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-22 : 10:57:19

Well you have a list of the files in the folder and a list of the stores expectd - you can work out from that which are missing.
And just insert the ones that are there.

I must be missing something - don't understand why the link above isn't most of what you need.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-22 : 12:05:50

need to think more about it Nigel but nothing is coming easy for me today.I apologise will sleep on it and let you know.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-22 : 17:16:04

I feel like a baby getting his nappy changed with this xp_cmdshell extended sproc I sincerely apologise everyone but going on Nigel's advise I have come up with this :

declare @result int
select @date = right('000' + convert(varchar(10), @date), 3)
select @date =''''+ '\\servername\Data\Irit\Data\HOSTIN\Itemsale\isale'+ cast(@storeno as char(3))+'.'+@date+''''
-- bulk insert using @date parameter
EXEC @result = master.dbo.xp_cmdshell 'DIR /B' + @date
IF (@result = 0)
begin
Set @sql='
BULK INSERT [tmpisaleimport]
FROM '+@date
Exec(@sql)
end
ELSE
begin

INSERT INTO itemsales_stores_input_error (storenumber)
VALUES (@storeno)
end

Please give me a small clue to why this is not working as I think my syntax error getting generated

( Server: Msg 170, Level 15, State 1, Procedure InsertItemsales, Line 42
Line 42: Incorrect syntax near '+'.)

is only the tip of the iceberg .....

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-06-23 : 03:35:29

quote:

EXEC @result = master.dbo.xp_cmdshell 'DIR /B' + @date

It's actually the EXEC command that's tripping you up. The only expressions you can use as SP parameter values are literals and variable names. You'll need to put the entire command in a variable rather than trying to concatenate the pathname in the EXEC call.

You're also missing a space between 'DIR /B' and the path name. And don't you need double quotes rather than single quotes around the pathname in the DIR command? (but still single ones in the BULK INSERT!)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-23 : 04:20:46

Also you will need a space after the /B

declare @cmd varchar(8000)
select @cmd = 'DIR /B ' + @date
exec master..xp_cmdshell @cmd

Also have a look at my code again. You need to put the result into a temp table and then look at what is there.
Also why do it for the specific store - do it one for all files then see if the store is there

create table #a (s varchar(1000))
select @date = right('000' + convert(varchar(10), @date), 3)
select @date =''''+ '\\servername\Data\Irit\Data\HOSTIN\Itemsale\isale*.'+@date+''''
declare @cmd varchar(8000)
select @cmd = 'DIR /B ' + @date
insert #a
exec master..xp_cmdshell @cmd

delete #a
where s not like 'isale%'
or s is null

delete #a
where s not in (select 'isale'+ cast(storeno as char(3))+'.'+@date+'''' from storelist)

-- now we have all the files to import
declare @filename varchar(1000) ,
@path varchar(1000)
select @path = '\\servername\Data\Irit\Data\HOSTIN\Itemsale\'
select @filename = ''
while @filename < (select max(s) from #a)
begin
select @filename = min(s) from #a where s > @filename

Set @sql='
BULK INSERT [tmpisaleimport]
FROM '+@path + @filename
Exec(@sql)
-- log that we imported file for this store
insert logtable
select event = 'imported', type = @filename, dte = getdate(), data = substring(@filename,6,3)
end

It's up to you8 whether you use #a to flag the ones that are not imported but I would use the log table.
Also log the number of rows imported.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-23 : 07:04:47

Ok Nigel will do thanks for your patience. I just need to get out of this cursor frame of mind and am finding it hard but realise it is the only way to go.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-23 : 11:15:32

Thanks you Nigel yours does work well and I will use it but I am a stubborn man and refuse to give up on mine I have added the space into mine with the extra variable in the cmdshell statement but it still doesnt acknowledge the file as being valid and all files are going into the itemsales_stores_input_error table can someone please put a stop to the pain please ?

select @date = right('000' + convert(varchar(10), @date), 3)
select @date =''''+ '\\servername\Data\Iris6\Data\HOSTIN\Itemsale\isale'+ cast(@storeno as char(3))+'.'+@date+''''
-- bulk insert using @date parameter
select @exists = 'DIR /B '+ @date
EXEC @result = master..xp_cmdshell @exists
IF (@result = 0)
begin
Set @sql='
BULK INSERT [tmpisaleimport]
FROM '+@date
Exec(@sql)
end
ELSE
begin

INSERT INTO itemsales_stores_input_error(storenumber)
VALUES (@storeno)
end

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-06-23 : 11:28:04

As I said, you're passing the DIR command to the shell with single quotes around the pathname. The shell filename quoting only works with double quotes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources