question

SQLLover21-0870 avatar image
0 Votes"
SQLLover21-0870 asked ·

SQL Server Table & Data

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?

sql-server-generalsql-server-transact-sqlsql-server-integration-servicessql-server-analysis-services
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

We have not hear from you , did you resolve your issue ?

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ·

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;

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I should have worded this questions better. I do not need the queries for importing the spreadsheet.

I would just like to know where I can look inside SQL server to find the answers to my questions. Please let me know if that makes sense.

For example, the one of my question: "what is the data refresh frequency of this table?"
-Where would I look to find out how often the data is refreshed?

One idea I had is that it could be associated with a SQL agent job, or a trigger. However there are no current jobs or triggers created against database or table to satisfy the answer to my question. Hope that makes sense

0 Votes 0 ·

It seems that you are looking for a needle in a haystack.

Jobs can run on a completely different from yours SQL Server instance.

Maybe your table in question has utility columns along the following: InsertedOn, UpdatedOn, etc.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.