October 9, 2015 at 6:11 am
So I have the following code below and I am getting the following error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '-'
What I am trying to do is bulk import all of the files in a folder that are tab delim into a table. They go into the temp table to be stored and then are referenced to be imported. But I get the error. Any help or ideas would be great on how to handle this. BTW, there are not many patches added on the server so options are limited.
CODE:
declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@cmd varchar(1000)
--get the list of files to process:
SET @path = '\\123.17.10.23\global$\Price_List_Files\output\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO tho_Vendor_Price_Files(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE tho_Vendor_Price_Files SET WHICHPATH = @path where WHICHPATH is null
--cursor loop
declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM tho_Vendor_Price_Files where WHICHFILE like '%.txt%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
begin
--bulk insert won't take a variable name, so make a sql and execute it instead:
set @sql = 'BULK INSERT tho_Vendor_Price_update FROM ''' + @path + @filename + ''' '
+ ' WITH (
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''''
) -U sa -P xxxxxxxxx -c'
print @sql
exec (@sql)
fetch next from c1 into @path,@filename
end
close c1
deallocate c1
October 9, 2015 at 6:18 am
BULK INSERT doesn't take parameters in the form -U, -P and -c. I think you're getting confused with bcp.
John
October 9, 2015 at 6:25 am
jonathanm 4432 (10/9/2015)
-U sa -P netfinity
Please say that's not the real password...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2015 at 6:28 am
It is not. Is the password I made up to my test db that I am testing on.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply