One way to do this would be Object Types, in this case the type would be analagous to your #t1. So it would need to be defined somewhere but it would not need to be global, it could be per-schema or per-procedure even. First, we can create a type:
SQL> create or replace type t1_type as object (x int, y int, z int)
2 /
Type created.
SQL> create or replace type t1 as table of t1_type
2 /
Type created.
Now set up some sample data:
SQL> create table xy (x int, y int)
2 /
Table created.
SQL> insert into xy values (1, 2)
2 /
1 row created.
SQL> insert into xy values (3, 4)
2 /
1 row created.
SQL> commit
2 /
Commit complete.
And create a function over this data returning our "temporary" type:
SQL> create or replace function fn_t1 return t1 as
2 v_t1 t1 := t1(); -- empty temporary table (really an array)
3 v_ix number default 0; -- array index
4 begin
5 for r in (select * from xy) loop
6 v_ix := v_ix + 1;
7 v_t1.extend;
8 v_t1(v_ix) := t1_type(r.x, r.y, (r.x + r.y));
9 end loop;
10 return v_t1;
11 end;
12 /
Function created.
And finally:
SQL> select * from the (select cast (fn_t1 as t1) from dual)
2 /
X Y Z
---------- ---------- ----------
1 2 3
3 4 7
As you can see this is pretty clunky (and uses collection pseudo-functions, which is an obscure feature at the best of times!), as I always say, porting from DB to DB is not merely about syntax and keywords in their SQL dialects, the real difficulty comes in different underlying assumptions (in the case of SQL Server, that cursors are expensive and their use avoided/worked around at all costs).
It sounds like you've got this entirely back to front and upside down. Typically changes are tracked using scripts, which you would then apply to the production database. I've never encountered a case where it made sense to import production data to the next version created from development.
If your database isn't currently in source control, the articles linked to from Jeff Atwood's article Get Your Database Under Version Control are a good introduction. Also, the free Redgate ebook SQL Server Team Based Development includes a chapter on source control for databases.
If you've been making changes via GUI tools and don't have any record of what's changed your best bet is a database comparison tool, like Redgate SQLCompare or Apex SQLDiff. These tools will generate the scripts to upgrade your production database to match the schema of development.
If the boss won't part with the cash for a comparison tool, you could reconcile the changes manually using a diff tool like WinMerge or DiffMerge. Use SSMS Generate Script tools to script objects to individual files, then use the diff tool to identify differences. Finally, hand craft the necessary ALTER statements for any changes to tables.
Best Answer
You could check out - https://www.attunity.com/products/replicate/attunity-replicate-for-microsoft-migration/
"Attunity Replicate for Microsoft Migrations is a special, no cost offering for Microsoft customers to help migrate data from popular databases to the Microsoft Data Platform."