Automated Bulk Insert - 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

General SQL Server Forums

New to SQL Server Programming

Automated Bulk Insert

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-17 : 15:03:35

declare @loc varchar(max)
set @loc = (select '''C:\SKUM\Ebay Inventory\'+(select (datename(MONTH, GETDATE()-1) +' '+ substring(CAST(getdate()-1 as varchar),5,3)+' Ebay Stuff'))+'\MW Priority.csv''')

BULK INSERT relisttable
FROM @loc
WITH
(

FIRSTROW = 6,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'

)

WHAT IS WRONG WITH THIS!!!!!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-17 : 15:21:49

A LOT!

The file name cannot be a variable.

Is your filename is really the following? (including the leading and trailing quote)

'C:\SKUM\Ebay Inventory\June 16  Ebay Stuff\MW Priority.csv'

CODO ERGO SUM

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-17 : 15:24:22

There is no way to make the file name variable? Really? No way? Because we are going to need to do like 40 bulk inserts a day, and changing the in 40 places every day seems.... not good.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-17 : 15:35:42

If the file name needs to be dynamic, then you have to use dynamic SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-17 : 16:03:51

Can that be written and executed on SQL server?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-17 : 16:06:27

quote:
Originally posted by ConradK

There is no way to make the file name variable? Really? No way? Because we are going to need to do like 40 bulk inserts a day, and changing the in 40 places every day seems.... not good.

Really. No way.

As Tara said, use dynamic SQL.

CODO ERGO SUM

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-17 : 17:10:22

declare @loc varchar(max)
set @loc = (select '''C:\SKUM\Ebay Inventory\'+(select (datename(MONTH, GETDATE()-1) +' '+ substring(CAST(getdate()-1 as varchar),5,3)+'Ebay Stuff'))+'\MW Priority.csv''')
declare @code nvarchar(1200)

set @code = N'use SKUM
drop table relisttable
create table relisttable
(
[Best-Match-sort-order] varchar(60) ,
[Item number] varchar(60) ,
Title varchar(60) ,
Price varchar(60) ,
Shipping varchar(60) ,
[Shipping type] varchar(60) ,
[Free shipping] varchar(60) ,
[Total quantity] varchar(60) ,
[Quantity available] varchar(60) ,
[Listing start date] varchar(60) ,
Format varchar(60) ,
[Listing end date] varchar(60) ,
[Category number] varchar(60) ,
[Listing category] varchar(60) ,
[Minimum Impressions] varchar(60) ,
[Maximum Impressions] varchar(60) ,
[Unique click-throughs] varchar(60) ,
[Unique click-through rate (%)] varchar(60) ,
[Number of watchers] varchar(60) ,
Sales varchar(60) ,
[Quantity sold] varchar(60) ,
[Sales impressions (%)] varchar(60) ,
[Sales unique click-throughs] varchar(60) ,
[View Item page] varchar(max) ,
[Revise listing] varchar(max) ,
[Listing analysis report] varchar(max)
)

BULK INSERT relisttable
FROM '+@loc+'
WITH
(

FIRSTROW = 6,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''

)
'

EXECUTE sp_executesql @code

am I doing it right?

now I just need to make the dynamic code dynamic that I can change out the mw with 5 different things, and then conversely change out the -1 with -1 though -7, and I am success!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-17 : 17:28:30

You mean like this:

declare @sql varchar(max)
set @sql=''
;with n(n) as (select 1 n union all select n+1 from n where n<7)
select @sql=@sql + replace('BULK INSERT relisttable FROM ''?'' WITH (FIRSTROW = 6, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'');', 
'?', 'C:\SKUM\Ebay Inventory\'+ datename(MONTH, GETDATE()-n) +' '+ datename(day,getdate()-n)+' Ebay Stuff\MW Priority.csv')
from n
print @sql
-- exec(@sql)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources