converting varchar to "string" for 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

SQL Server 2008 Forums

Transact-SQL (2008)

converting varchar to "string" for bulk insert

Author

Topic

mtcoder
Starting Member

19 Posts

Posted - 2010-10-11 : 09:46:23

I have a ftp site and want to bulk insert comma deliminated files. I have a table which has the Municipalities name and I create a parameter which is the name + current date in a certain format. The Muni_table controls which Municipalities are ftp and needs loaded. I use a cursor to run through the table and pick out the @Name value. All of this works perfectly. I then try to use the @Filename for the from section of Bulk Insert but it errors saying it expects Integer, String, ID, etc etc.
How can I use the @filename to pull the files in? Here is my code.

Declare @FileName as varchar(max)
Declare MuniCursor Cursor Fast_Forward
for
Select Muni_Name from muni_information where Active = 1 and configuration_Type = 'FTP'
Declare @Name as varchar(max)
Open MuniCursor
fetch Next from MuniCursor into @Name
While (@@FETCH_STATUS <> -1)
Begin
if (@@FETCH_STATUS <> -2)
Set @FileName = 'C:\' + @Name + '_' + convert(varchar(2),DATEPART(mm,getdate()))+ '_' + convert(varchar(2),DATEPART(DD,GETDATE())) + '_' + CONVERT(varchar(4), DATEPART(YY,getdate())) + '.txt'
bulk insert Initial_DataLoad
from @FileName
with (ROWTERMINATOR = '\n', FieldTerminator = '\t')
Fetch Next From MuniCursor into @Name
End
Close MuniCursor
Deallocate MuniCursor
go

bulk insert Initial_DataLoad
from @FileName
with (ROWTERMINATOR = '\n', FieldTerminator = '\t')
Fetch Next From MuniCursor into @Name
End

Is the trouble part the from @filename throws the error.

Thanks for help

mtcoder
Starting Member

19 Posts

Posted - 2010-10-11 : 10:28:50

ok found out what I needed.

Set @SQL = 'bulk insert #tmpMuni from ''' + @FileName + '''
with (CodePage = ''OEM'', ROWTERMINATOR = ''\n'', FieldTerminator = ''\t'')'
exec sp_executesql @SQL

Basically just use dynamic sql for the running of the bulk insert

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources