locked
Using Stored Procedure to bulk insert file data into SQL Server Temp Table? RRS feed

  • Question

  • User-718146471 posted

    Hey folks, I have this .CSV file for importing into my temp table. I know how to create a temp table, declare my variables and all that. What I am needing some help on is how to do a Bulk SQL Copy inside a Stored Procedure. My code at this point looks like this:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE sp_CreateImportTempTable 
    	-- Add the parameters for the stored procedure here
    (
    	@CVECount int null,
    	@ContentVulnCVE varchar(50) null,
    	@ContentVulnCheckName varchar(150) null,
    	@Vendor varchar(150) null,
    	@Software varchar(150) null,
    	@Bucket varchar(150) null,
    	@PenTested varchar(150) null,
    	@Apptype varchar(150) null,
    	@AppID varchar(150) null,
    	@LevelVal varchar(150) null,
    	@Summary varchar(MAX) null
    )
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	CREATE TABLE #CVETempTable
    	(
    		TempCVEID int Identity (1,1) Primary Key,
    		CVECount varchar(150), 
    		ContentVulnCVE varchar(150),
    		ContentVulnCheckName varchar(150),
    		Vendor varchar(150),
    		Software varchar(150),
    		Bucket varchar(150),
    		PenTested varchar(150),
    		Apptype varchar(150),
    		AppID varchar(150),
    		LevelVal varchar(150),
    		Summary varchar(MAX)
    	)
    
        -- Insert statement for procedure here
    	INSERT into #CVETempTable(CVECount, ContentVulnCVE,ContentVulnCheckName,Vendor,Software,Bucket,PenTested,Apptype,AppID,LevelVal,Summary)
    
    	SELECT @CVECount, @ContentVulnCVE,@ContentVulnCheckName,@Vendor,@Software,@Bucket,@PenTested,@Apptype,@AppID,@LevelVal,@Summary
    	
    END
    GO
    

    Thursday, September 22, 2016 12:57 PM

Answers

  • User-718146471 posted

    Since I figured it out, I am going to post my solution here in case others need to refer to it. Trust me, this is lightning fast.

    ALTER PROCEDURE [dbo].[sp_ImportData]
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	CREATE TABLE #ImportData
    	(
    		CVECount varchar(MAX), 
    		ContentVulnCVE varchar(MAX),
    		ContentVulnCheckName varchar(MAX),
    		Vendor varchar(MAX),
    		Software varchar(MAX),
    		Bucket varchar(MAX),
    		PenTested varchar(MAX),
    		Apptype varchar(MAX),
    		AppID varchar(MAX),
    		LevelVal varchar(MAX),
    		Summary varchar(MAX)
    	)
    
        -- Insert statements for procedure here
    BULK INSERT #ImportData from 'C:\MVMCount.csv' WITH
    (
    	FIELDTERMINATOR = ',',
    	FIRSTROW = 2
    )
    SELECT * FROM #ImportData
    DROP TABLE #ImportData
    END
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 22, 2016 4:51 PM