SQL Server Performance Forum – Threads Archive
Comparing Values in the ColumnsNeed some suggesttions. I have a table CREATE TABLE Symbols
TradeDate DATETIME ,
ProcessIndicator CHAR(1) ,
Col1 VARCHAR(10) ,
) Here Col1 and Col2….Coln are all the other columns ( Say Symbol Properties ) Ex Here is my Question , I have to create a Delta File on a daily basis and send it out. Say First time when I load the table , we have 2000 symbols for the TradeDate 12/07/2006 and I create the file for these 2000 symbols and send it out. Next day 1e on 12/08/2006 I have a total of 2500 symbols out of which we have 500 New Sysbols whch we just append to the existing table. For the Rest of the 2000 sybols I have to compare the values of each Columns and if there is a Diff then I have to send the 500 New Symbols and the Symbols where the properties have changed. #. What is the best way to compare the columns for already existing symbols
#. What is the best way to mark the lines which are modified. Any Sugestions as to how to approach this. Thx Much
You should probably have a look at adding a column of the datatype timestamp to your table. Newly added records you could filter with WHERE NOT EXISTS() and modified rows with OR t1.timestamp <> t2.timestamp. —
Microsoft SQL Server MVP
What you can do is create another copy of the table with everything same. and copy your data in the new table . Then next day when you get the new data added in your original table use JOINS to compare the two tables and get the newly added rows which are not in the newly created rows.
You can do it on daily basis that you truncate the test table and feed new data after calculation so that your test table is up to date till yesterday’s data or you can simply append new data.
see if this makes sense to you. Thanks
Its good but If I re-run the process on the same day … that will be an Issue. V
One other possible solution is create another job with following steps. 1>Create a simple dts package which will first clear everything from <second table> and the bring the latest data from the original table.
2> Wrtie query with joins and conditions you want as filters and insert the results into <third table> Schedule the job whatever time suits you. Then all you have to do is just
select * from <third table> May be i made things lil complicated but this what i feel and let me now the best solution you find. Thanks
I would go with Frank’s suggestion. IIRC, he’s been working with this specific type of data for years – and notwithstanding that he’s pretty good at T-SQL.[<img src=’/community/emoticons/emotion-4.gif’ alt=’‘ />]
Frank/Adriaan, Can you please give me an Example for this ( or the Pseudo code ) Thx
After adding the timestamp(datetime field) column in the table, you can select/update the data based on that timestamp.
SELECT DISTINCT Col1
WHERE NOT EXISTS
WHERE t1.timestamp <> t2.timestamp)
Look BOL for EXISTS. Thanks,