adamn123
asked on
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') '.
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') '.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok got it to work if I make the value
'c:\CSVLoad\1\04-23-10-10-
not
c:\CSVLoad\1\04-23-10-10-3
need to modify the code to auto put in the ' '
'c:\CSVLoad\1\04-23-10-10-
not
c:\CSVLoad\1\04-23-10-10-3
need to modify the code to auto put in the ' '
ASKER
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
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
ASKER
SET @SQL = 'BULK INSERT Serials_temp FROM ''' + @fileloc + ''' WITH (FIELDTERMINATOR = ''\n'')'
Thanks all for the help :)
Thanks all for the help :)
ASKER
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-3