Databases
StackOverflow2013
Postsdbo
POTemp table to Master table insert or update with auditing and logging
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTemp table to Master table insert or update with auditing and logging
    primarykey
    data
    text
    <p>I have to select the records from <code>temptable</code> and insert or update to <code>mastertable</code>. If a single field is changed, then I need to update my <code>history</code> table with the following:</p> <ul> <li><code>RecordID</code></li> <li><code>ColumnChanged</code></li> <li><code>OldValue</code></li> <li><code>NewValue</code></li> </ul> <p>While performing the insert or update from the <code>temptable</code> to the <code>mastertable</code>, any errors that's occur due to foreign key violations (or any other errors) should be logged to the <code>error</code> table.</p> <p>I need the history of changes and logging of errors, so that if a single record fails, I can rerun the process again.</p> <p>How can I do this using T-SQL? Any code snippets will be helpful.</p> <p>Here are the tables columns that I'm working with:</p> <ol> <li><p><strong>TEMPTABLE</strong><br /> COL1, COL2, COL3, COL4, COL5</p></li> <li><p><strong>MASTERTABLE</strong><br /> COL1, COL2, COL3</p></li> <li><p><strong>HISTORY</strong><br /> RecordID, ColumnChanged, OldValue, NewValue</p></li> <li><p><strong>ERROR</strong><br /> ErrorCode, ErrorMsg</p></li> </ol> <p>I need to use a cursor, as I need to loop through each record when performing the inserts or updates, and to log any errors that happen during the inserting or updating due to bad data.</p> <p>I need to mark the <code>flagcolumn</code> as "Y" for all successful insert or updates and "N" for all the failed records so that I can reload them again after correcting the data.</p> <p>I've already used <code>sqlbulkcopy</code> to load the data in to the <code>temptable</code>.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAlexander
    1. USJohn
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTemp table to Master table insert or update with auditing and logging
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POTemp table to Master table insert or update with auditing and logging
      1. USJohn
      1. VTBountyStart
    3. VO
      singulars
      1. POTemp table to Master table insert or update with auditing and logging
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFor this type of auditing you shouldnt need a cursor, Depending on what flavor of SQL Server you have you should be able to use the output clause http://msdn.microsoft.com/en-us/library/ms177564.aspx
      singulars
      1. POTemp table to Master table insert or update with auditing and logging
      1. USgh9
    2. COYou didn't specify which are the primary keys in the TEMPTABLE and the MASTERTABLE (even if there aren't any primary keys defined, we need to know which columns should be considered as part of the primary keys). Also, there are more columns in the TEMPTABLE than the MASTERTABLE; should we ignore the additional columns?
      singulars
      1. POTemp table to Master table insert or update with auditing and logging
      1. USRazvan Socol
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload