0

I want to know what is the best practice to insert multiple rows from a source table into a destination table. Both source and destination tables are on a production database meaning delete or update transactions are being performed 24/7 on the tables. Also the destination table has an auto_incremented column which means if a Rollback transaction executes, the Rollback does not decrement the auto incremented column - find details here 

BEGIN TRY 
    BEGIN TRANSACTION
    Insert (col1,col2,col3) into destinationTable
    select col1,col2,col3 from sourceTable
    COMMIT TRANSACTION
    PRINT 'TRANSACTION COMMITTED'
END TRY


BEGIN CATCH
      ROLLBACK TRANSACTION
      PRINT 'TRANSACTION ROLLED BACK'  
END CATCH
wwkudu
  • 2,778
  • 3
  • 28
  • 41
shaadi
  • 161
  • 2
  • 4
  • 21
  • Have a look at bulk insert. – John Jun 22 '17 at 06:39
  • Of course this is the best way. Who cares about gaps in a sequence? – dnoeth Jun 22 '17 at 06:52
  • What number of records are you talking about? What is the problem with your approach? Have you considered (or do you need to) [reset the auto increment value](https://stackoverflow.com/questions/13858062/reset-autoincrement-in-microsoft-sql-server-2008-r2) – wwkudu Jun 22 '17 at 06:55
  • If you wish to have records from the `Source Table` to the `Destination table` as an when the data gets inserted into the `Source` then you can make use of the `Trigger`. – DataWrangler Jun 22 '17 at 07:35
  • If speed is the issue here (i'm not quite sure what the problem is with skipping rows) then have you considered the SWITCH TO statement ALTER TABLE [SOURCETABLE] SWITCH TO [DESTINATION TABLE] It does not hit the logs so it is a very fast way of transferring data between tables. https://stackoverflow.com/questions/41348523/how-does-alter-table-switch-works-on-sql-server – MrKobayashi Jun 22 '17 at 08:19

0 Answers0