SQL Server - Using INSERT command within SELECT, for any conditions

[sivi]

I would be grateful for any help in the following:

1. I need to create additional records (probably using INSERT command) within a SELECT statement depending on a particular condition / situation. I normally use CASE statements within a SELECT statement to handle different conditions. The newly created record will be populated with a combination of literal values and the values from other fields of the first record.

2. I need to create one record from a set of records. The scenario is, we store all the changes, made to any table, on a particuler day in the audit trail table. The table is will store the ,Key field of the master table, field name, type of change (Add, Edit or Delete), Old value and New value of this field in one record. Separate records will show the different field name of any master table. I need to bring all the rows (records) belong to any key field (i.e. one row in the master table) into one row, showin the old & new values of all the fields on one single row.

I hope this explains.

I would appreciate if someone could help me to solve these problems.


Sivi

 

[billchris]

sivi,

I usually jump into these questions without enough information and end up answering the wrong question based on invalid assumptions. So, this time I'm going to try something different and clarify.

You have an audit table that looks something like this, correct?

Audit
--------
AuditId
MasterTableName
MasterTableRecordId
ChangedFieldName
ChangeType
OldValue
NewValue

And you want do a select from it that returns one row for a given MasterTableName and MasterTableRecordId. That row will have three columns for each ChangedFieldName: ChangedFieldname, OldValue, NewValue.

Or something close to that.

Please point out my erroneous assumptions, then we can proceed.




“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal