Filename Concatenation - bulk insert

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

Filename Concatenation - bulk insert

Author

Topic

taggs
Starting Member

7 Posts

Posted - 2010-05-20 : 10:42:09

Hi,
I am trying to set up a job to run a bulk insert each day from a csv file created elsewhere. I am not sure if I am going about it the right way but I'm not sure how to concantenate the file name for the import (as it will be different each day). SQL is complaining about the '+' in the file name.

Any help is much appreciated
Taggs


Declare @date varchar(10)
set @date = convert(varchar(10),getdate(),105)
print @date

BULK Insert HDD_Info_temp 
from 'c:\support\bulkimport\HDD-REPORT-SUCCESS_' + @date + '-09-17-00.csv'
with 
(fieldterminator = ',',rowterminator ='\n')
go

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 13:28:40

You'll need to build the BULK INSERT command into a variable and then use dynamic SQL to execute it.

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

Subscribe to my blog

taggs
Starting Member

7 Posts

Posted - 2010-05-24 : 02:44:34

Thanks Tara, I'll give it a go.

taggs
Starting Member

7 Posts

Posted - 2010-05-24 : 03:15:28

All sorted now thanks, it only took me 5 minutes to sort out once I was on the right track


Declare @date varchar(10),@BulkCSVImport varchar(200)
set @date = convert(varchar(10),getdate(),105)

set @BulkCSVImport = 'BULK Insert HDD_Info_temp from ''c:\support\bulkimport\HDD-REPORT-SUCCESS_' + @date + '-09-17-00.csv'' with (fieldterminator = '','',rowterminator =''\n'')'
Exec (@BulkCSVImport)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-24 : 12:46:03

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

Subscribe to my blog

zstarsales04
Starting Member

20 Posts

Posted - 2010-05-25 : 02:36:41

spam removed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources