Post
Ask Question

Select Insert Query Using a Stored Procedure

This snippet is a query which does select Insert operation into a table. We here would see that creating a Stored procedure which actually selects the data from a table and may be insert into the same table or other tables with identical columns. here I would show you how to insert into the same table.

This is really handy when there is a huge amount of records to be selected and inserted and using foreach in Entity Framework would hamper the performance heavily.
  1. ALTER PROCEDURE [dbo].[spAddRecordsForNewX]    
  2.     -- Add the parameters for the stored procedure here    
  3.     @fkID INT,    
  4.     @conditionID INT,    
  5. AS    
  6. BEGIN    
  7. --================== SELECT INSERT QUERY FOR THE RESPECTIVE TABLE RECORDS=================--    
  8.  BEGIN TRANSACTION    
  9.     INSERT INTO    
  10.     dbo.[TABLE#]    
  11.  SELECT    
  12.     @fkID,    
  13.     pRel.Column1,     
  14.     pRel.Column2,     
  15.     pRel.Column3,    
  16.     pRel.Column4,    
  17.     pRel.Column5,       
  18.  FROM    
  19.     dbo.[TABLE#] as pRel    
  20.  WHERE    
  21.     PRIMARYKEY = @conditionID --This would be a primary key condition to retrive and Insert the codes    
  22.  IF @Rollback = 1     
  23.     ROLLBACK TRANSACTION    
  24.  ELSE    
  25.     COMMIT    
  26. //The transanction is trackerd and rolled back if any issue   
I hope this helps.
Please use it and improve performance.
Thanks