0

I need to perform some calculations using few columns from a table. This database table that gets updated every couple of hours generates duplicates on couple of columns every other day. There is no way tell which one is inserted first which affects my calculations.

Is there a way to copy these rows into a new table automatically as data gets added every couple of hours and perform calculations on the fly? This way whatever comes first will be captured into a new table for a dashboard and for other business use cases.

I thought of creating a stored procedure and using a job scheduler to perform this. But I do not have admin access and can not schedule jobs. Is there another way of doing this efficiently? Much appreciated!

Edit: My request for admin access is being approved.

WallStreetBets
  • 9
  • 2
  • 1
    It sounds like you want to use a trigger as suggested in https://stackoverflow.com/a/1651909/212514. Alternatively you might consider a `merge` statement https://stackoverflow.com/a/14806962/212514. – user212514 Jul 24 '20 at 01:37
  • This would require a trigger. It's unlikely you'll be able to add one, if you don't have the privileges required to schedule tasks. Contact your DBA to create it for you if you don't. – Ken White Jul 24 '20 at 03:57
  • Thank you both user212514 and @KenWhite for your replies. I will check if it works. Also, could you provide me with a reference to the use cases? I am searching hard but unable to find one. Much appreciated! – WallStreetBets Jul 24 '20 at 05:56
  • If by *use cases* you mean *examples*, you can use your favorite search engine to find those. We've pointed you in the direction of the wather; it's up to you to start drinking. :-) – Ken White Jul 24 '20 at 11:19
  • @KenWhite Fair enough! I thought you may have something handy :) – WallStreetBets Jul 24 '20 at 16:25
  • A search of this site for `[sql-server] create trigger` turns up almost 10K results. I think you can probably find one yourself. :-) – Ken White Jul 24 '20 at 16:34
  • now when you have admin access,then you can do couple of thnig in orignal table,add identity column and datetime column then you can easily do CDC.After this you can do what not – KumarHarsh May 08 '23 at 11:45

2 Answers2

0

Another way as to stated in the answers, what you can do is:

  1. Make a temp table.
  2. Make a prod table.
  3. Use stored procedure to copy everything from the temp table into prod table after any load have been done.
  4. Use the same stored procedure to clean the temp table after the load is done.

Don't know if this will work, but this is in general how we are dealing with huge amount of load on a daily basis.

Ivan Ivanov
  • 131
  • 1
  • 2
  • 6
0
INSERT INTO stage (id,name,price,date)
SELECT id,name,price,date
FROM job
WHERE NOT EXISTS(
    SELECT 1
    FROM stage
    WHERE stage.date=job.date
    )
abolfazl sadeghi
  • 2,277
  • 2
  • 12
  • 20
RAJA RAJESWARI
  • 1
  • 3
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 05 '23 at 20:31