locked
hello RRS feed

  • Question

  • I have Similar question

    I want to insert record from one table  to another table on same sql database and It will insert data 20 to 100 per day .Mainly I have 13 to 30  Datetime field   for each table.There is no dependency in data.

    Which approach is better job or trigger? i don't have any new transaction within 15 sec ?


    • Edited by kd05 Friday, June 2, 2017 5:31 PM
    Friday, June 2, 2017 5:29 PM

Answers

  • A job will make the transfer asynchronous. With a trigger it will be synchronous. The trigger will have some overhead affecting every transaction doing any modifications on the table.

    If you are doing to use a job you will need to be able to track changes - if there is a rowversion column on the source table this will make it easy, but you must handle deletes. It is not clear to me that your datetime field will be a basis to move rows - for example - will an update update the datetime field - will the update be transaferred as an update?

    I would say the choice should be made on how up to date you want the destination table to be. If you want it up to date, a trigger is the best choice. If you can live with the two tables being out of date - use a job.

    Friday, June 2, 2017 5:46 PM
  • Based on your requirement, you only need data in TableB at the end of each month, so you can create a job to copy data from TableA to TableB daily, weekly or even monthly since the number of rows to be copied is small.

    A Fan of SSIS, SSRS and SSAS

    Monday, June 5, 2017 2:04 PM

All replies

  • If you need the information in real time in other table , use job to make copy of records at intervals.

    Trigger also an option, but it comes with over head. I don't use trigger if I don't need data in other table immediately


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, June 2, 2017 5:41 PM
  • Your description is not clear. Provide more details about what you want to do.

    A Fan of SSIS, SSRS and SSAS

    Friday, June 2, 2017 5:44 PM
  • A job will make the transfer asynchronous. With a trigger it will be synchronous. The trigger will have some overhead affecting every transaction doing any modifications on the table.

    If you are doing to use a job you will need to be able to track changes - if there is a rowversion column on the source table this will make it easy, but you must handle deletes. It is not clear to me that your datetime field will be a basis to move rows - for example - will an update update the datetime field - will the update be transaferred as an update?

    I would say the choice should be made on how up to date you want the destination table to be. If you want it up to date, a trigger is the best choice. If you can live with the two tables being out of date - use a job.

    Friday, June 2, 2017 5:46 PM
  • In Table A insert is the only option ,I am not updating any record neither delete .I just need to transfer all the data into table b with same format that table A has but without dependency  meaning I have two same table one has some dependency and other has no dependency is more like flat tables .all data should be there in flat table before end of the month so i can generate extract of that data on monthly basis.

    • Edited by kd05 Friday, June 2, 2017 6:27 PM
    Friday, June 2, 2017 6:26 PM
  • You mean both tables have the same columns?

    A Fan of SSIS, SSRS and SSAS

    Friday, June 2, 2017 7:47 PM
  • If you are just doing a monthly extract you should be able to do on off the base table.

    Otherwise a truncate of the destination table followed by a select into should work if the table is small.

    ie

    truncate table tableb

    insert into tableb

    select * from tablea

    Friday, June 2, 2017 7:50 PM
  • Yes Both have same column 
    Friday, June 2, 2017 8:04 PM
  • Based on your requirement, you only need data in TableB at the end of each month, so you can create a job to copy data from TableA to TableB daily, weekly or even monthly since the number of rows to be copied is small.

    A Fan of SSIS, SSRS and SSAS

    Monday, June 5, 2017 2:04 PM