Insert new record in table depending upon conditions in another

Question

I've searched all over and come up with a blank. I am trying to update one record and insert others in one table depending upon conditions in another table. Both tables have a common RecordID (an nvarchar as they include characters).

Easiest explained in In English:

• If record ID1 has a value in ColumnA AND ColumnB AND ColumnC in TableA
• Then, in TableB, update ColumnBla with the value from TableA.ColumnA
• Then insert a new record, copying all fields from record ID1, plus adding the suffice 'A' to the RecordID
• Then, in this new record, update CollumnBla with the value from TableA.ColumnB
• Repeat with another new record for the value from TableA.ColumnC

There are other conditions where a record has a value in ColumnA AND ColumnB but not in ColumnC etc. in which case only one new record need be added, but they should come out in the wash.

Answer 1

You can insert values into a table by using insert with select query.

So the queries for your scenario will look like the below ones.

     create table @RecordsUpdated(RecordID nvarchar(max))

     update t
     set ColumnBla = TableA.ColumnA
     output
         inserted.RecordID 
         into @RecordsUpdated
     from 
         TableB
         inner join TableA on TableB.RecordID = TableA.RecordID
     where
         TablesA.ColumnA is not null
         and TablesB.ColumnA is not null
         and TablesC.ColumnA is not null

     insert into TableB(RecordID, ColumnBLa)
     select TableA.RecordID + 'A', ColumnB
     from
       TableA
       inner join @RecordsUpdated on @RecordsUpdated.RecordID = TableA.RecordID

     insert into TableB(RecordID, ColumnBLa)
     select TableA.RecordID + 'A', ColumnC
     from
       TableA
       inner join @RecordsUpdated on @RecordsUpdated.RecordID = TableA.RecordID