Hello,
I got help from a forum building the SQL Server script below.
begin transaction;
begin try
select &marketplace as marketplace, orderno, orderitemid as orderitemno, uuid, oid, invoicedate, year(current_timestamp) "year", totalprice, invoiceemail, sendername, xml, efatura, earsiv, ordercancelledafterinvoicing as ordercancelled, current_timestamp "archivedate"
into #itemstobearchived
from orders1
where invoiced = '1' and uploaded = '1';
delete o
output a.marketplace, a.orderno, a.orderitemno, a.uuid, a.oid, a.invoicedate, a.year, deleted.totalprice, deleted.invoiceemail, deleted.sendername, :entegrator, a.efatura, a.earsiv, deleted.xml, '0', a.ordercancelled, a.archivedate into einvoicearchive
from orders1 o
join #itemstobearchived a on a.orderno = o.orderno and a.orderitemno = o.orderitemid;
end try
begin catch
rollback transaction;
end catch
if @@TRANCOUNT > 0
commit transaction;
begin try
drop table #itemstobearchived;
end try
begin catch
end catch
Now, I need to convert this script to do the same on a FirebirdSQL v4.0.
What I need to do is basically select "invoiced" and "uploaded" records from the "ORDERS" table, insert them into the "ARCHIVE" table and delete only those inserted records from the "ORDERS" table. This operation is manually run by the user from time to time. Since there are more than one orders table (one for each marketplace application supports) I did not keep the temporary table created because column names are changing in SQL Server version.
I am thinking of using CTE for selecting records for insertion. But, I am not sure if I can use the same CTE for deleting records to get rid of creating a temporary table.
I appreciate any help.
Thanks & Regards,
Ertan