Using output&into clause to insert data to two tables

Question

i would like to move some staging data to production table and delete staging data and inserting history data for production table at the same time if it is possible. i want to do something like below:

DELETE FROM _stagingTable
OUTPUT deleted.idTest, deleted.textTest
INTO _productionTable(idTest,textTest)
--Below is what im not sure
OUTPUT 'Add',deleted.idTest, deleted.textTest
INTO _productionTableHistory(typeTest, idTest, textTest)

Is it possible to use output clause more than once? Or what do you suggest me to do?

Thanks in advance

The stated duplicate (http://stackoverflow.com/questions/17165870/multiple-output-clauses-in-merge-insert-delete-sql-commands) may be informative, but I fail to see how it addresses the question of moving the deleted records from the staging table into another table. — Gordon Linoff, May 16 '16 at 14:41

score 0 · Accepted Answer · answered May 16 '16 at 14:05

0

The answer is no you cannot directly do what you want.

If you do not want to use a trigger solution and you do not want a query based on a primary key then I think you want a temp table.

Output into #TempHoldTable, then insert into table1 from #TempHoldTable and insert into table2 from #TempHoldTable.

answered May 16 '16 at 14:05

Joe C

3,925
2
11
31

Thank you so much. It seems that this is the most logical way for me – Erdem Gündoğdu May 20 '16 at 07:38

Gordon Linoff · Answer 2 · 2016-05-16T14:31:19.667

-1

I think this is the idea:

DELETE FROM _stagingTable
    OUTPUT deleted.idTest, deleted.textTest
    INTO _productionTable(idTest, textTest);

INSERT INTO _productionTableHistory(typeTest, idTest, textTest)
    SELECT 'Add', idTest, textTest
    FROM _productionTable pt;

These are two separate statements. You can wrap them into a single transaction, if you need to.

If you are concerned about data in _productionTable, then truncate it before using it in the output clause. Given that it starts with an underscore, I find it hard to believe that the intention is not a temporary place just for this statement, but I don't know the naming conventions being used.

edited May 16 '16 at 14:31

answered May 16 '16 at 14:01

Gordon Linoff

1,242,037
58
646
786

2

This would have a problem of grabbing records from _productionTable that were loaded previous to this 'transaction'. – Joe C May 16 '16 at 14:06
@JoeC . . . So empty the table before doing the insert. The `_` suggests that it is a temporary table anyway. – Gordon Linoff May 16 '16 at 14:30
1

_ does not mean that to me. Without that being specified in the question, production table implies that it will have records from multiple runs. – Joe C May 16 '16 at 14:44

Using output&into clause to insert data to two tables

2 Answers2

Linked