SQL Server 2014 Job

Question

SQL Server 2014 Job

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I need please if you can give me ideas on how to solve the following:

I have a database that I will call Principal that is in SQL Server , it imports information from other databases ( Oracle and SQL Server ) to show it in a system that only manipulates and shows the information in different ways (reports, graphs, etc.).

The import of this data is done through a Job that runs every 2 hours (this schedule is strictly necessary since the information changes constantly).

The Job takes around 10 minutes to finish executing and import the new information, the problem is that in that period of time the users can not consult data because the Job I have it the following form ( example ):

TRUNCATE TABLE Principal
INSERT INTO Principal
SELECT * FROM TablaDeOtraBaseDatos

Also, if an error occurs with Job I no longer have data.

Any ideas on how to solve this? Or if I'm doing badly the Job ?

Thank you.

sql sql-server

asked by Alonso Fallas 05.05.2017 в 21:01

source

3 answers

0

do not tune the table before the information is emptied, you have several options! The ones that come to mind right now are:

Option 1: I run the job and fill in different tables with similar names, so after I finish the job, I can pass that information to the real tables! That is, a) .- I run the work that lasts 10 minutes, at the end of the process, truncates and copies each of the tables, that will be a matter of seconds.

Option 2: You can use a column in each table that serves as a reference to determine which record is old and which record is new, that is, configure one of the columns of type INT and in DEFAULT in INSERT that is assigned the number 0 in each of the tables that take the reports, I configure that the report (s) take only the records that have the value 1 in the new column, so, in the JOB , I fill in all the tables, then I delete all the records that contain the value 1 , and then, I update all the existing records with value 0 to 1 and it will be violent.

answered by 05.05.2017 в 23:08

0

I would recommend that instead of doing that process you have a temporary table in the example bd: Certificate_Temp the main one would be Certificate and do the following process:

Truncate the temporary table

Load the information in the temporary table

Execute an SP that makes the mixture between the two tables using the SQL merge function Merge statement

Merge mix two tables in one, you can indicate which fields you want to update if the value exists or if they have to be deleted or inserted, depending on the variables you choose, in addition to the end it shows you what action I take in each row update, delete or insert. This will take care of doing the hard work for you, an example would be:

MERGE dbo.Certificate AS T
    USING Certificate_Temp AS S
    ON (T.SerialNumber = S.Id) 
    WHEN NOT MATCHED BY TARGET 
        THEN INSERT(SerialNumber,EffectiveDate,ExpirationDate,Expired,Revoked,Subject,UserId,Type) 
        VALUES(S.Id, S.EffectiveDate,S.ExpirationDate,S.Expired,S.Revoked,S.Subject,@UserId,'S')
    WHEN MATCHED 
        THEN UPDATE SET T.ExpirationDate = S.ExpirationDate,T.EffectiveDate = S.EffectiveDate, T.Expired = S.Expired,T.Revoked = S.Revoked
    --WHEN NOT MATCHED BY SOURCE
        --THEN DELETE 
    OUTPUT $action, inserted.*, deleted.*;

This way your table will never be inaccessible

answered by 30.06.2017 в 23:12

How to get the id of the created elements? OutOfMemoryError using base64 and realmdb when displaying images on android

score 1 · Accepted Answer

I have implemented something similar in the following way:

I create a table PrincipalTemporal , it has to be a real physical table identical copy of Principal

I carry out the import of the data in PrincipalTemporal

I verify the correct functioning of this process

If the information was incorporated correctly: a) DROP TABLE Principal and then sp_rename 'PrincipalTemporal', 'Principal'

This way in case of error we did not destroy the table, of course we will have outdated information, but according to the scenario this may have less importance to not having anything.