I am using SQL Server 2012. I would like to move some some master-detail records into their archive tables. Archive tables does not contain all identical columns. They hold some reduced data.
Before archive operation both tables are linked on orderid column (exists in both master and detail tables with same column name). After archive operation there is no more orderid column but there is uuid column and mater-detail link should be on that uuid column (exists on master table, does not exists on detail table).
Below statements works for moving header records
insert into einvoicearchive select uuid, current_timestamp, year(current_timestamp), invoiceamount, invoicetype, buyeremail, fullname, :entegrator, xml from orders where cast(lastinvoicesendstatus as varchar(5)) = 'OK'; delete from orders where cast(lastinvoicesendstatus as varchar(5)) = 'OK';
What I am after is to add two more statements below above ones for moving detail records. So far, I could select detail records using below statement
select 'UUID_of_header_record_to_be_here', productid, productname, quantity, price, calculatedtaxamount, calculatedpricewithouttax from ordersdetail where orderid not in (select orderid from orders)
What I am missing in selected detail records is linked uuid column.
I am kind of confused as to if I can solve this without coding but using SQL statements only.
I am also after kind of fast execution and appreciate suggestions as to this. I only have index on orderid column on both master and detail tables.
Any help is appreciated.
Thanks & Regards,