Insert and update records in a single TSQL statement?

Question

I have a BigTable table and a LittleTable table. I want to move a copy of some records from BigTable to LittleTable, and then (for these records) set BigTable.ExportedFlag to T (indicating that the copy of the record was moved to a small table).

Is there a way to do this in one statement?

I know that I can complete a transaction:

• moves records from a large table based on where clause
• updates a large table parameter exported to T based on the same where clause.

I also looked at the MERGE statement , which doesn’t look quite right, because I don’t want to change the values ​​in a small table, just move the entries to the table.

I reviewed the OUTPUT clause after the update statement, but cannot find a useful example. I don’t understand why Pinal Dave uses Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal instead of Updated.TextVal . Is updating a review of insertion or deletion?

I found this TSQL: UPDATE post with INSERT INTO SELECT FROM saying "AFAIK, you cannot update two different tables with the same sql statement".

Is there any clean syntax for this? I am looking for the correct, supported SQL query. Should I transfer two statements in one transaction?

Answer 1

You can use the OUTPUT clause as long as LittleTable meets the requirements that should be the goal of OUTPUT ... INTO

 UPDATE BigTable SET ExportedFlag = 'T' OUTPUT inserted.Col1, inserted.Col2 INTO LittleTable(Col1,Col2) WHERE <some_criteria> 

It doesn't matter if you use INSERTED or DELETED . The only column it will differ in is the one you are updating ( deleted.ExportedFlag has a value of up and inserted.ExportedFlag will be T )