make bulk insert work from a value

what am I doing wrong?
I am sure it is something simple but I can not find a solution
I am trying to make the following code

--Bulk insert into Test
BULK INSERT Serials_temp
FROM 'C:\test.csv'
WITH
(
FIELDTERMINATOR=',',
ROWTERMINATOR = '\n'
)
DECLARE @my_user_value VARCHAR(50)
SET @users  = ('adam')
 
INSERT INTO Serials (serial, users)
SELECT serial, @users
FROM Serials_temp

but make 'C:\test.csv'  be the value @fileloc






set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[serialupload]
(
     @users VARCHAR(50),
     @fileloc VARCHAR(50)
 )
AS
BEGIN

--Bulk insert into Test
DECLARE @SQL varchar(2000)
SET @SQL = "BULK INSERT Serials_temp FROM '"+@fileloc+"' WITH (FIELDTERMINATOR = '\n') "
EXEC (@SQL)

 
INSERT INTO Serials (serial, users)
SELECT serial, @users
FROM Serials_temp

END

I am getting error


Msg 207, Level 16, State 1, Procedure serialupload, Line 11
Invalid column name 'BULK INSERT Serials_temp FROM ''.
Msg 207, Level 16, State 1, Procedure serialupload, Line 11
Invalid column name '' WITH (FIELDTERMINATOR = '\n') '.
adamn123Asked:
Who is Participating?
 
RiteshShahCommented:
double quote wouldn't work in SQL Server. you have to use single quote for string... in the case of dynamic SQL, you have to use, two time single quote.


SET @SQL = 'BULK INSERT Serials_temp FROM ' + @fileloc + ' WITH  (FIELDTERMINATOR = ''\n'')'

before \n, there is not a double quote, it is a two time single quote. actually you can consider it escape sequence like you used to have it in c#, c, c++ etc.
0
 
mdagisCommented:
Replace

SET @SQL = "BULK INSERT Serials_temp FROM '"+@fileloc+"' WITH (FIELDTERMINATOR = '\n') "

With

SET @SQL = 'BULK INSERT Serials_temp FROM ' + @fileloc + ' WITH (FIELDTERMINATOR = ''\n'')'
0
 
adamn123Author Commented:
I am getting error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'c'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

I ran it and if  it helps the fileloc I put as c:\CSVLoad\1\04-23-10-10-39-44-serial.csv  and users was adam
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
adamn123Author Commented:
ok got it to work if I make the value

'c:\CSVLoad\1\04-23-10-10-39-44-serial.csv'\
not
c:\CSVLoad\1\04-23-10-10-39-44-serial.csv

need to modify the code to auto put in the ' '
0
 
adamn123Author Commented:
can someone help me so the code puts the ' ' for me
I tried many ways I thought would work but does not
don't want to upload teh value to have them want the insert to have them please
0
 
adamn123Author Commented:
SET @SQL = 'BULK INSERT Serials_temp FROM '''  + @fileloc +  ''' WITH (FIELDTERMINATOR = ''\n'')'

Thanks all for the help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.