December 23, 2008 at 4:01 am
Hi friends,
i need to import some file every day from csv to sql table. That file will be different file every day with todays date as last name.
i.e. los_export_2008_12_23 & los_export_2008_12_24 etc.
What is the best idea to use this import except SSIS?
Thanks,
vijay
December 23, 2008 at 4:03 am
I tried this.
declare @file as varchar(100)
set @file = '\\server01\wcl\Extracts\Executive\LOS-export' + convert(varchar, datepart(yyyy,getdate())) + '-' + right(('0' + convert(varchar, datepart(mm, getdate()))),2) + '-' + right(('0' + convert(varchar, datepart(dd, getdate()))),2) + '.csv'
print @file
BULK
INSERT LOS_try
FROM @file
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
It didnt work. I am not sure how to use @file into bulk insert command?
thanks,
vijay
December 23, 2008 at 9:52 am
dva2007 (12/23/2008)
I tried this.declare @file as varchar(100)
set @file = '\\server01\wcl\Extracts\Executive\LOS-export' + convert(varchar, datepart(yyyy,getdate())) + '-' + right(('0' + convert(varchar, datepart(mm, getdate()))),2) + '-' + right(('0' + convert(varchar, datepart(dd, getdate()))),2) + '.csv'
print @file
BULK
INSERT LOS_try
FROM @file
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
It didnt work. I am not sure how to use @file into bulk insert command?
thanks,
vijay
try something like this:
DECLARE @file as varchar(100)
SET @file = '\\server01\wcl\Extracts\Executive\LOS-export' + CONVERT(VARCHAR, DATEPART(yyyy,GETDATE())) + '-' + RIGHT(('0' + CONVERT(VARCHAR, DATEPART(MM, GETDATE()))),2) + '-' + RIGHT(('0' + CONVERT(VARCHAR, DATEPART(DD, GETDATE()))),2) + '.csv'
PRINT @file
DECLARE @cmd varchar(1000)
SET @cmd = 'BULK INSERT LOS_try
FROM '''+ @file +'''
WITH (FIELDTERMINATOR = '','',
ROWTERMINATOR = '''+CHAR(10)+'''
)'
EXEC(@cmd)
* Noel
January 12, 2009 at 5:53 am
Noeld: You are right, but can you shed some light on why it is necessary to resort to dynamic SQL to get this to work?
I have hit the same problem in a couple of places (BULK INSERT, and I think one other statement)
where a string containing a file name is required, but the string cannot be the result of an expression, and must instead be formed using dynamic SQL.
What in the statement syntax in BOL warns me that this is going to be the case?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply