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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.