Bulk insert using stored procedure

I have a query which is working fine:

BULK INSERT ZIPCodes 
FROM  'e:\5-digit Commercial.csv' 
WITH 
( 
     FIRSTROW = 2 ,
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n' 
)

but now I want to create a stored procedure for it.

I have written below code to make its stored procedure:

create proc dbo.InsertZipCode
@filepath varcha开发者_StackOverflowr(500)='e:\5-digit Commercial.csv'
as
begin
BULK INSERT ZIPCodes 
FROM  @filepath 
WITH 
( 
     FIRSTROW = 2 ,
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n' 
)
end

but its showing error:

Msg 102, Level 15, State 1, Procedure InsertZipCode, Line 6 Incorrect syntax near '@filepath'.

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

Please tell me what I am doing wrong and what I can do to make it work in stored procedure.

Thanks

---

There's nothing wrong with your stored procedure code - the point is: the BULK INSERT command cannot accept a file name as a variable.

This does work:

BULK INSERT ZIPCodes 
FROM  'e:\5-digit Commercial.csv' 
WITH 

but this never works - within a stored proc or not:

DECLARE @filename VARCHAR(255)
SET @filename = 'e:\5-digit Commercial.csv' 

BULK INSERT ZIPCodes 
FROM @filename
WITH 

So you just cannot do it this way, unfortunately. You could consider building up your BULK INSERT statement as a string (with a fixed file name) and then execute it as dynamic SQL - but I don't really see any other solution.

DECLARE @filepath nvarchar(500)
SET @filepath = N'e:\5-digit Commercial.csv'

DECLARE @bulkinsert NVARCHAR(2000)

SET @bulkinsert = 
       N'BULK INSERT ZIPCodes FROM ''' + 
       @filepath + 
       N''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'

EXEC sp_executesql @bulkinsert

---

There is an alternative to dynamic SQL if you have access to the SQLCmd exe.

The SqlCmd utility allows you to pass string replacement variables using the -v argument.

You can use a template variable named "filepath" which will be replaced when you execute the script via the cmdline.

The SQL script would look like:

BULK INSERT ZIPCodes 
FROM  '$(filepath)' 
WITH 
( 
     FIRSTROW = 2 ,
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n' 
)
end

You would then execute the script from a commandline using something like the following:

sqlcmd -b -S SERVER\INSTANCEHERE -E -i "PATH\FILENAMEHERE.Sql" -v FilePath = "e:\5-digit Commercial.csv" -s "|"

The important part of the example is the -v argument:

-v FilePath = "e:\5-digit Commercial.csv"

---

You just try it out, I think you need to upload this CSV file directly to the 'E' drive. For that you need to have admin rights, I think, or ask someone who is in database administration.

create procedure dbo.InsertZipCode
AS
BEGIN
SET NOCOUNT ON;
 BULK
   INSERT ZIPCodes from 'e:\5-digit Commercial.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)
END

---

create PROC TestInsert
    (
      @stuName NVARCHAR(50) ,
      @XmlData XML
    )
AS
    BEGIN
        BEGIN TRY 
            INSERT  INTO dbo.Test_Student
                    ( stuName 
                    )
            VALUES  ( @stuName
                    );
            DECLARE @id BIGINT;
            SET @id = ( SELECT  SCOPE_IDENTITY()
                      ); 
            INSERT  INTO dbo.Test_Qual
                    ( stuid ,
                      stuclass ,
                      InstituteId ,
                      obtmark ,
                      totalmark ,
                      per
                    )
                    SELECT  @id ,
                            col.value('stuclass[1]', 'nvarchar(50)') AS stuclass ,
                            col.value('InstituteId[1]', 'int') AS InstituteId ,
                            col.value('obtmark[1]', 'nvarchar(100)') AS obtmark ,
                            col.value('totalmark[1]', 'nvarchar(50)') AS totalmark ,
                            col.value('per[1]', 'nvarchar(50)') AS per
                    FROM    @XmlData.nodes('Parent/child') AS Doc ( col );  

            SELECT  @id AS RegisIdNUH ,
                    1 AS Flag ,
                    'Save' AS Msg
            FROM    dbo.Test_Student R
            WHERE   R.stuid = @id;

        END TRY
        BEGIN CATCH
            SELECT  0 AS Flag ,
                    'Some Error occur' AS Msg;
            ROLLBACK;
        END CATCH;
    END;