SQL Server Table & Data

Question

We have a table that is managed by a different company that we are accompanying. Every month, there is a spreadsheet that needs to be imported from the official source into the INFO table.

In Production there is a call being made and the call is working perfectly. However in DEV & UAT the call is not working correctly. The requirement now is create a new table in DEV & UAT with the same info in PROD. The spreadsheet is already uploading to the data warehouse on a schedule, so we could potentially be ingesting it directly from there instead of needing to manually process a spreadsheet.

When I check DEV and UAT, there is a INFO table that includes the same table/structure/data as in PROD. The data in DEV & UAT are repeated and inconsistent. So I am confused as to why I would need to create the same table.

How/Where can I figure out the answers to these set of questions below in SQL Server?:
-Is the data in this table modified in any way?
-What is the data refresh frequency of this table?
-Do they keep historical data for each month this gets loaded? e.g. to do need to run a query to always grab the latest INFO table?

Answer 1

It is a good process redesign. It is better not to deal with spreadsheets.
Spreadsheets are like electronic paper that accept any scribbles.

You can leverage T-SQL MERGE statement. It will identify all the changes and what to do with them, i.e. INSERT/UPDATE/DELETE in one single statement.

Here is a conceptual example for you.

SQL

-- DDL and sample data population, start
--Create a target table
DECLARE @Products TABLE (
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Rate MONEY
);

INSERT INTO @Products
VALUES (1, 'Tea', 10.00),
   (2, 'Coffee', 20.00),
   (3, 'Muffin', 30.00),
   (4, 'Biscuit', 40.00);

--Create source table
DECLARE @UpdatedProducts TABLE (
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Rate MONEY
);

INSERT INTO @UpdatedProducts
VALUES   (1, 'Tea', 10.00),
   (2, 'Coffee', 25.00),
   (3, 'Muffin', 35.00),
   (5, 'Pizza', 60.00);
-- DDL and sample data population, end

--SELECT * FROM @Products;
--SELECT * FROM @UpdatedProducts;


--MERGE SQL statement
--Synchronize the target table with
--refreshed data from source table
;MERGE INTO @Products /* WITH (UpdLock, HoldLock) */ AS TARGET  
USING @UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
-- When rows are matched, update the rows if there is any change
-- but only if something needs to be updated!!!
WHEN MATCHED AND (TARGET.ProductName <> SOURCE.ProductName 
   OR TARGET.Rate <> SOURCE.Rate) THEN 
   UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
         TARGET.Rate = SOURCE.Rate
--When no rows are matched, insert the incoming rows from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN 
   INSERT (ProductID, ProductName, Rate) 
   VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same row does not exist in source table
--then delete this row from target table
WHEN NOT MATCHED BY SOURCE THEN 
   DELETE
--$action specifies a column of type nvarchar(10) 
--in the OUTPUT clause that returns one of three 
--values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
--according to the action that was performed on that row
OUTPUT $action, 
   DELETED.ProductID AS TargetProductID, 
   DELETED.ProductName AS TargetProductName, 
   DELETED.Rate AS TargetRate, 
   INSERTED.ProductID AS SourceProductID, 
   INSERTED.ProductName AS SourceProductName, 
   INSERTED.Rate AS SourceRate;
-- INTO #log (a, dt, what);

/*
We can see the results:
- Coffee rate was updated from 20.00 to 25.00, 
- Muffin rate was updated from 30.00 to 35.00, 
- Biscuit was deleted 
- Pizza was inserted.
*/
SELECT * FROM @Products;

Answer 2

I tend to agree with Yithzak about the needles and haystacks. It is difficult for use to answer, since this is related to a specific application about which we don't have any knowledge.

The best way seem to be to ask the other company for documentation.

If that is not possible, spying on the process in production with Trace is an approach, but if you have never worked with Trace before this can a little difficult. Not the least, because that casual use of Profiler can cause performance issues, which I don't think you want in production.