bulk insert with a parameter - 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 with a parameter

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-07 : 07:29:37

Hi guys I have a short bit of code that works fine up until the bulk insert part takes place, can someone please advise what i am doing wrong please
Here it is:

drop table tmpofrimport
create table tmpofrimport ([salesdata] [char] (56))
declare @date as varchar(100)
declare @sql as varchar(200)

select
@date = '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.'+ 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()))

Set @sql='
BULK INSERT [tmpofrimport]
FROM '+@date
Exec(@sql)

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-07 : 07:38:51

Strange but this bulk insert now works with the added highlighted lines, I don't understand ?

drop table tmpofrimport
create table tmpofrimport ([salesdata] [char] (56))
declare @date as varchar(100)
declare @sql as varchar(200)

select
@date = '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.'+ 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'+@date,3)
select @date =''''+ '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.'+@date+''''

Set @sql='
BULK INSERT [tmpofrimport]
FROM '+@date
Exec(@sql)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-07 : 07:43:26

Try displaying what you are executing.
cast(datediff(day,min(date),getdate()) as varchar(3))
= 7
select @date = right('000'+@date,3)
= 007

select
@date = '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.'+ cast(datediff(day,min(date),getdate()) as varchar(3))

s.b.
select
@date = '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.'+ right('000' + cast(datediff(day,min(date),getdate())as varchar(3)),3)

==========================================
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-07 : 08:06:02

No problem nr, by the way your custom sp_who2 procedure to show a block has worked great for me I have nearly eliminated all blocks in one of our applications , thank you sincerely.

Here is what I am getting as requested:

drop table tmpofrimport
create table tmpofrimport ([salesdata] [char] (56))
declare @date as varchar(100)
declare @sql as varchar(200)

select
@date = '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.'+ cast(datediff(day,min(date),getdate()) as varchar(3))
-- @date = \\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.129
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'+@date,3)
--@date = 129
select @date =''''+ '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.'+@date+''''
--@date = '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.129'
Set @sql='
BULK INSERT [tmpofrimport]
FROM '+@date
Exec(@sql)

The thing is though if I remove the first @date = statement after the select and just use:

SELECT '\\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.'+ cast(datediff(day,min(date),getdate()) as varchar(3))

I don't get anything entered into the tmpofrimport table through the bulk insert which is confusing me as @date is still the same value before the bulk insert takes place no matter if I leave the first @date out or not
Any ideas ?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-07 : 08:13:44

I thought you were probably dropping leading zeroes on the day number - but your extra statements wouldn't have corrected that anyway.
I can only suspect there are some non display characters in your first @date variable.

==========================================
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.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-07 : 08:17:17

Doh.
It's because you have added the single quotes around the filename in the second statement.
select @date = '''' + @date + ''''

would do just as well. The right and other stuff is just a red herring.

==========================================
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-07 : 08:59:53

I know I have just realised what a idiot I am sometimes, thank you for pointing that out .
One more question please Nigel if you don't mind if date in the @date variable is \\jy2003smem012\Data\Iris6\Data\HOSTIN\Itemsale\isale.29 after the first statement right but after the second one select @date = right('000'+@date,3) I get date = 29 rather than 029....
Is there any simple way to get a extra zero if there is only a two digit number please ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources