column info from temp table

Hello,
i have a stored proc where i create two temp tables with complete equal
structure. But the structure is different every time the stored proc is
called. Now i want to find all records in temp table 1 which are not in
temp table 2.
I am not sure if the sorting in both tables is equal. So i fear that i have
to compare one record from one table with all records from the other table
field by field.
How can i do this? Or is there a better solution?
thanks,
HelmutIf you are using SQL Server 2005 you can use except operator to compare
tables.
Regards
Amish Shah|||Hi, Helmut
Temporary tables are stored in tempdb.
You can find the columns of temporary tables like this:
CREATE TABLE #T1 (X INT, Y INT)
SELECT c.name FROM tempdb.dbo.sysobjects o
INNER JOIN tempdb.dbo.syscolumns c ON c.id=o.id
WHERE o.name LIKE '#T1%'
However, if tables with the same name are created by two different
processes at the same time you need to consider also the suffix of the
table, which is different for each process (is based on the "connection
number", i.e. the value of @.@.CONNECTION when the connection is made;
also, a part of the suffix is based on @.@.NESTLEVEL). I do not know any
reliable method of determining this suffix in a live environment
(because the value of @.@.CONNECTION may have been changed since the
beginning of the connection).
Razvan|||Helmut,
As you say, the structure may be different each call bot the tables are
identical, so you may well know the likely PK available.
Apart from that point, it may be useful to know what leads to you populating
two tables in any case. Why can you not take the comparison back one step an
d
attempt the comparison there?
If you want to reveal the basic SProc code here it may well be a lot clearer
to assist you,
Tony
"helmut woess" wrote:

> Hello,
> i have a stored proc where i create two temp tables with complete equal
> structure. But the structure is different every time the stored proc is
> called. Now i want to find all records in temp table 1 which are not in
> temp table 2.
> I am not sure if the sorting in both tables is equal. So i fear that i hav
e
> to compare one record from one table with all records from the other table
> field by field.
> How can i do this? Or is there a better solution?
> thanks,
> Helmut
>|||Am 3 Feb 2006 06:48:52 -0800 schrieb amish:

> If you are using SQL Server 2005 you can use except operator to compare
> tables.
> Regards
> Amish Shah
Arrrrgghhh, i can't use SQL 2005, i am bound to SQL 2000 :-(
But good to know that in future this problem not longer exists.
thanks,
Helmut|||Hi, Razvan
thank you for this info, i will see if i can find out more about it. But it
shed some light in my problem :-)
Helmut|||Am Fri, 3 Feb 2006 07:11:07 -0800 schrieb Tony Scott:
...
> Apart from that point, it may be useful to know what leads to you populati
ng
> two tables in any case. Why can you not take the comparison back one step
and
> attempt the comparison there?
The starting point you can see if you look some entries down, it is the
problem building the Oracle MINUS functionality for SQL Server 2000.
I am short before finishing a first solution and then i will post it here
in hope that it will become much more clearer what i need and maybe some
people can help me to make it more universal, more stable, ... and much
more faster :-)
bye,
Helmut|||"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138979281.680288.12130@.o13g2000cwo.googlegroups.com...
> Hi, Helmut
> Temporary tables are stored in tempdb.
> You can find the columns of temporary tables like this:
> CREATE TABLE #T1 (X INT, Y INT)
> SELECT c.name FROM tempdb.dbo.sysobjects o
> INNER JOIN tempdb.dbo.syscolumns c ON c.id=o.id
> WHERE o.name LIKE '#T1%'
> However, if tables with the same name are created by two different
> processes at the same time you need to consider also the suffix of the
> table, which is different for each process (is based on the "connection
> number", i.e. the value of @.@.CONNECTION when the connection is made;
> also, a part of the suffix is based on @.@.NESTLEVEL). I do not know any
> reliable method of determining this suffix in a live environment
> (because the value of @.@.CONNECTION may have been changed since the
> beginning of the connection).
> Razvan
or you can use object_id() function:
SELECT c.name FROM tempdb.dbo.sysobjects o
INNER JOIN tempdb.dbo.syscolumns c ON c.id=o.id
WHERE o.id=object_id('tempdb..#T1')
dean|||Hi, Dean
You are right. It's much better this way, since it eliminates the
problem of multiple temporary tables with the same name.
Razvan|||Am Fri, 3 Feb 2006 19:28:16 +0100 schrieb Dean:

> SELECT c.name FROM tempdb.dbo.sysobjects o
> INNER JOIN tempdb.dbo.syscolumns c ON c.id=o.id
> WHERE o.id=object_id('tempdb..#T1')
Oh yes, perfect.
thanks,
Helmut