locked
Bulk Inserts in a Stored Procedure RRS feed

  • Question

  • Hi

    In my current stored procedure, it is inserting data into two tables. This is done in steps. Below are steps

    For 1st table, two temp tables #A and #B are joined and then inserted into another temp table #C. From #C data will be inserted into target table. 

    The process is same for other table as well. 

    Can we use BULKINSERT to populate these tables ? If so please advise...

    Thanks

     

    Tuesday, March 3, 2020 5:33 PM

Answers

  • Hi AlwayesLearner08,

    Is you data in a file that you want populate into a table  ? if yes then you can do it for example you have a CVS file and contains many data rows.


    BULK INSERT tblSales
    
    FROM 'C:\Users\Username\Desktop\sales20.csv'
    
    WITH (
    
    FIRSTROW = 2,
        FIELDTERMINATOR = ',',
    	ROWTERMINATOR='\n' ,
    	    BATCHSIZE=300000 );
     
    1. tblSales is the target table ( BULK INSERT tblSales )
    2.  Specify the CSV file path  ( FROM 'C:\Users\Username\Desktop\sales20.csv') 
    3. Specify index of row which row you want to be first row for example in CSV file first row always is header (name of the columns ) this should be skipped that's why FIRSTROW = 2
    4. FIELDTERMINATOR Used to separate columns from each other for example ( 1/2/2009 6:17,Product1,1200,Mastercard,carolina ) ( , )
    5. BATCHSIZE = 300000 batch_size Specifies the number of rows in a batch Each batch is copied to the server as one transaction this will be change per your size of rows in CSV file
      I hope this helps you.


    Please remember to mark the replies as answers if they helped you :) ~

    Tuesday, March 3, 2020 7:22 PM
  • Hi AlwaysLearner08,

    BULK INSERT imports a data file into a database table or view in a user-specified format, if your tables are all physical tables in SQL Server, there is no need to use "BULK INSERT", "INSERT ... SELECT * FROM" should work.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    • Edited by Lily Lii Wednesday, March 4, 2020 5:53 AM
    • Marked as answer by AlwaysLearner08 Thursday, March 5, 2020 7:25 PM
    Wednesday, March 4, 2020 5:53 AM

All replies

  • Hi AlwayesLearner08,

    Is you data in a file that you want populate into a table  ? if yes then you can do it for example you have a CVS file and contains many data rows.


    BULK INSERT tblSales
    
    FROM 'C:\Users\Username\Desktop\sales20.csv'
    
    WITH (
    
    FIRSTROW = 2,
        FIELDTERMINATOR = ',',
    	ROWTERMINATOR='\n' ,
    	    BATCHSIZE=300000 );
     
    1. tblSales is the target table ( BULK INSERT tblSales )
    2.  Specify the CSV file path  ( FROM 'C:\Users\Username\Desktop\sales20.csv') 
    3. Specify index of row which row you want to be first row for example in CSV file first row always is header (name of the columns ) this should be skipped that's why FIRSTROW = 2
    4. FIELDTERMINATOR Used to separate columns from each other for example ( 1/2/2009 6:17,Product1,1200,Mastercard,carolina ) ( , )
    5. BATCHSIZE = 300000 batch_size Specifies the number of rows in a batch Each batch is copied to the server as one transaction this will be change per your size of rows in CSV file
      I hope this helps you.


    Please remember to mark the replies as answers if they helped you :) ~

    Tuesday, March 3, 2020 7:22 PM
  • Hi Rebin,

    Thanks for the reply.

    In both cases no .csv files are being used to populate tables. All the temp tables are created using physical tables. These temp tables are used to populate target tables.

    Tuesday, March 3, 2020 7:50 PM
  • In my current stored procedure, it is inserting data into two tables. This is done in steps. Below are steps

    For 1st table, two temp tables #A and #B are joined and then inserted into another temp table #C. From #C data will be inserted into target table. 

    The process is same for other table as well. 

    Can we use BULKINSERT to populate these tables ? If so please advise...

    No. BULK INSERT is a tool for reading data from files on disk into tables. You would use plain INSERT statements for your task.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Naomi N Wednesday, March 4, 2020 12:37 AM
    Tuesday, March 3, 2020 10:45 PM
  • Hi AlwaysLearner08,

    BULK INSERT imports a data file into a database table or view in a user-specified format, if your tables are all physical tables in SQL Server, there is no need to use "BULK INSERT", "INSERT ... SELECT * FROM" should work.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    • Edited by Lily Lii Wednesday, March 4, 2020 5:53 AM
    • Marked as answer by AlwaysLearner08 Thursday, March 5, 2020 7:25 PM
    Wednesday, March 4, 2020 5:53 AM
  • Hi AlwaysLearner08,

    Do the answers above help you? It's so kind of you to mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, March 5, 2020 6:40 AM