1

We have a 17 store system which are on SQL Server 2005 Standard Edition and on Windows 2003 server. We currently have the following process:

  1. At each store we have an SQL job which runs a procedure to populate 1 table. Procedure basically first Truncates the table and then does the Insert from the system. This has records from day our store was open till now, approximately 2million records in this table. There is no way to archive it.
  2. We then have an SSIS package which gets this table data from store's back to Home Office every night. It runs for 4-7hours depending upon connectivity, which is slow most of the time.

We would like to do the following:

  1. We would want to create a separate table at the store, which would record ONLY the changes occurred to table its populating every night (above Step-1)
  2. This way, when SSIS package pull the data, it doesn't have to pull back 2million records, instead it can only pull back the changes

Due to SQL Server 2005 we cannot use MERGE command and as table gets Truncated and Inserted is not the good candiate for replication. How should we get ONLY change back to the home office?

Thank you, Hiren Patel

  • Can you make modifications to the source table(s)? I'm thinking of adding a single TIMESTAMP column. – Max Vernon Dec 29 '15 at 19:27
  • If you upgrade to a supported version of sql server 2012 or 2014, there will be smart methods to help you. Win 2003 is almost dead ... so plan for an upgrade. – Kin Shah Dec 29 '15 at 19:49
  • Kin, It all on Operation group, we have told them many times about limitation and support but its hard to convince them – NismoGTR05 Dec 29 '15 at 19:52
2

I would change the process to be more like the following. Not the best solution, but there aren't that many good options to begin with.

Import Data Process: a) - Drop yesterday's table b) - rename current table to *_yesterday c) - Import from system to current

Create view in Store's SQL Server that will return only rows that are new/different between current and _yesterday's tables. Something like the below.

SELECT Current.*
FROM Current
LEFT OUTER JOIN Yesterday ON Yesterday.PK = Current.PK (or list each field out seperately)
WHERE Yesterday.PK IS NULL (so the new ones)
OR ISNULL(Yesterday.Field1, '') <> ISNULL(Current.Field1, '')
<repeat>

2) - Move data to HQ a) - Use view created to just pull across only the new records (possibly into staging table). Then use merge? (is HQ a higher version of SQL?) to push the newly updated data into HQ's copy of the data.

  • Jonathan, Yes the Home Office system is on SQL Server 2008R2 Enterprise Edition – NismoGTR05 Dec 29 '15 at 19:49
  • Jonathan, I have [Store] and [RTOID] as a PK, so my query would be like this: SELECT c.* FROM h_dc_newimast c LEFT OUTER JOIN dc_newimast y ON y.store = c.store and y.rtoid = c.rtoid -- (or list each field out seperately) WHERE y.store IS NULL --(so the new ones) OR ISNULL(y.store, '') <> ISNULL(c.store, '') OR ISNULL(y.rtoid, '') <> ISNULL(c.rtoid, '') ? – NismoGTR05 Dec 29 '15 at 20:00
  • That's just about it, but you want to check for both join fields to be NULL, so your WHERE clause would include WHERE y.store IS NULL OR y.rtoid IS NULL OR ISNULL(y.<field1>, '') <> ISNULL(c.<field1>, '') <and repeat> – Jonathan Fite Dec 30 '15 at 15:37
1

Assuming you can make changes to the source tables involved, you might want to consider adding a TIMESTAMP column to each source table, and using that to track changed rows. The TIMESTAMP column is automatically updated by the server whenever any column is modified.

By way of an example:

USE tempdb;

DROP TABLE dbo.T1;

CREATE TABLE dbo.T1
(
    T1_ID INT NOT NULL
        CONSTRAINT PK_T1
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData VARCHAR(255)
    , T1_TS TIMESTAMP NOT NULL
);

INSERT INTO dbo.T1 (SomeData)
VALUES ('This is a test 1');
INSERT INTO dbo.T1 (SomeData)
VALUES ('This is a test 2');
INSERT INTO dbo.T1 (SomeData)
VALUES ('This is a test 3');
INSERT INTO dbo.T1 (SomeData)
VALUES ('This is a test 4');

SELECT *
FROM dbo.T1

CREATE TABLE dbo.T1_MergedRows
(
    T1_ID INT NOT NULL
    , T1_TS VARBINARY(8) NOT NULL
);

SELECT *
FROM dbo.T1 
WHERE NOT EXISTS (
    SELECT *
    FROM dbo.T1_MergedRows t1m 
    WHERE t1m.T1_TS = t1.T1_TS
    );

UPDATE dbo.T1_MergedRows 
SET T1_TS = T1.T1_TS
FROM dbo.T1_MergedRows t1m
    INNER JOIN dbo.T1 ON t1m.T1_ID = t1.T1_ID
WHERE t1m.T1_TS <> T1.T1_TS;

INSERT INTO dbo.T1_MergedRows (T1_ID, T1_TS)
SELECT t1.T1_ID
    , t1.T1_TS
FROM dbo.T1
WHERE NOT EXISTS (
    SELECT *
    FROM dbo.T1_MergedRows t1m 
    WHERE t1m.T1_TS = t1.T1_TS
    );


SELECT *
FROM dbo.T1 
WHERE NOT EXISTS (
    SELECT *
    FROM dbo.T1_MergedRows t1m 
    WHERE t1m.T1_TS = t1.T1_TS
    );

UPDATE dbo.T1 
SET SomeData = 'This is not a test'
WHERE T1.T1_ID = 3;

SELECT *
FROM dbo.T1 
WHERE NOT EXISTS (
    SELECT *
    FROM dbo.T1_MergedRows t1m 
    WHERE t1m.T1_TS = t1.T1_TS
    );

The results of the 4 SELECT statements above:

enter image description here

As you can see in the image above, the last select statement only returns rows that have been modified. So the workflow for this would be:

  1. Rows get inserted/updated/deleted throughout the day.

  2. The nightly process runs this to get new/modified rows:

SELECT * FROM dbo.T1 WHERE NOT EXISTS ( SELECT * FROM dbo.T1_MergedRows t1m WHERE t1m.T1_TS = t1.T1_TS );

  1. Update the T1_MergedRows table:

UPDATE dbo.T1_MergedRows SET T1_TS = T1.T1_TS FROM dbo.T1_MergedRows t1m INNER JOIN dbo.T1 ON t1m.T1_ID = t1.T1_ID WHERE t1m.T1_TS <> T1.T1_TS;

INSERT INTO dbo.T1_MergedRows (T1_ID, T1_TS) SELECT t1.T1_ID , t1.T1_TS FROM dbo.T1 WHERE NOT EXISTS ( SELECT * FROM dbo.T1_MergedRows t1m WHERE t1m.T1_TS = t1.T1_TS );

If you need to track deletions, you could modify the T1_MergedRows table to include an IsDeleted bit column, then inspect that during the process that imports rows. You would update the T1_MergedRows table to set the IsDeleted bit in Step 3 above, like this:

UPDATE dbo.T1_MergedRows 
SET IsDeleted = 1
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.T1
    WHERE T1.T1_ID = T1_MergedRows.T1_ID
    );

Your nightly import process could "see" what rows were deleted by looking at:

SELECT *
FROM dbo.T1_MergedRows
WHERE IsDeleted = 1;
  • I thought of that, but the way the OP described truncating the table each night and importing all data from the "system" made me think that this is an intermediate stage system and the Source of the data cannot be made more restrictive. – Jonathan Fite Dec 29 '15 at 19:46
  • You're probably correct. I just wanted to provide a possible way of doing it. Have a +1 – Max Vernon Dec 29 '15 at 19:47
  • Jonathan, You are right, we cannot modify the Source table as store are using 3rd party software ILS which doesn't allow that. – NismoGTR05 Dec 29 '15 at 19:48
  • Your solution would work if i know how to get the information on DELETED record at the store. With your query i can see the new INSERT or Changes, but I don't see what got deleted at the store, as I would have to delete it from here at the home office. Can you please help me with that? Thank you, HP – NismoGTR05 Dec 29 '15 at 21:46
  • I have updated my answer to reflect how you might handle deletions. – Max Vernon Dec 29 '15 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.