TSQL- help please.. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

TSQL- help please..

I am trying to sort out the logic of comparing two tables and updating it based on the output the other, and get this into a tsql statement. Say we have BaseTable, LogTable, and #SnapshotTable. 1. Initiate LogTable by doing insert into..select * from BaseTable…
2 Snapshot table gets all data from BaseTable.
3. Compare #SnapShotTable to LogTable
4. #Snapshot has rows that do not exist in LogTable, therefore add the rows to LogTable and flag it as new (ie ‘N’)
5. LogTable has rows that #SnapShotTable DOES NOT, therefore update LogTable with flag ‘deleted’ (ie ‘D’) Trying to avoid cursors. Can I use exists? THX
Something along these lines:<br />For 4:<br /><pre id="code"><font face="courier" size="2" id="code">INSERT INTO LogTable &lt;column list&gt;<br />SELECT &lt;column list&gt;, ‘N’ FROM LogTable <br />WHERE &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rimarykey&gt; NOT IN (SELECT primarykeycolumn FROM #SnapShotTable )<br /></font id="code"></pre id="code"><br /><br />For 5:<br /><pre id="code"><font face="courier" size="2" id="code"><br />UPDATE LogTable<br />SET Flag =’D'<br />WHERE primarykeycolumn NOT IN (SELECT PrimaryKeyColumn FROM #SnapShotTable )<br /></font id="code"></pre id="code"><br /><br /><br /><br /><br /><br />***********************<br />Dinakar Nethi<br />SQL Server MVP<br />***********************<br /<a target="_blank" href=http://weblogs.sqlteam.com/dinakar/>http://weblogs.sqlteam.com/dinakar/</a>
Perfect, ndinakar! I used a NOT EXISTS b/c I don’t have a pkey, but the concept works. One more question: What if the value DOES exist In the #SnapshotTable and the LogTable (where LogTable.Flag=’D’)
and you want to insert a new row for this where flag is ‘N’? THX
same as above, but with
WHERE LogTable.Flag=’D’
condition. ***********************
Dinakar Nethi
SQL Server MVP
Thx for reply. Not sure if that will work. Please follow:<br /><br />User Flag Action D_Date<br /><br />1. UserA N Open 5/7/2007 t1<br />2. UserA D Close 5/7/2007 t2<br />3. UserA N ReOpen 5/7/2007 t3<br />4. UserA D Close 5/7/2007 t4<br /><br />Now in the above example, where flag=’D’ will insert (2) additional rows, where I just want one row inserted, but the values exist already. I want part of my logic to add a new row for a change in the comparative results, and add a new entry w/new date-time. It’s probably easier to do an UPDATE, but want to keep a running log. Does this make sense? (I’m confusing myself <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />EDIT: I think what I really want to do is record the deltas. What changed. If the two table are different, record the difference in the LogTable. Hopefully this makes more sense. Appreciate your help!
I’m still struggling with this. Please advise… "I think what I really want to do is record the deltas. What changed. If the two table are different, record the difference in the LogTable. (new row for each action-have a running log of all changes) Hopefully this makes more sense. Appreciate your help!" OK, I think I’m ok now. I need a previous state table, and a current state table, and a log table. I think I was trying to use the prev state and log in one table, and that made the effort more complicated. Many thanks again!
The "snapshot" concept is available in triggers, but IIRC in SQL 2005 you can also access those snapshots from within your procedure. Can’t help you with the syntax, I’m a still a 2000 guy.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]