Combining two Tables into one TempTable, with a condition.

Hi,

I'm very new to Sql, and need to combine information from two tables into one temp table. All tables have the same structure.

The problem is I want to set a condition that if Field 1 is already in the TempTable, dont include that field from the second table. Along the lines of

Table1 Description = Blue, if Table 2 Description = Blue, dont include this row.

I have been trying to use INSERT..SELECT and UNION. , the only problem I have is that I cannot come up with a working conditional statement to prevent both complete sets of data being written to the TempTable.

I'm using Sql Server 2005 deveoper Edition and VS2005Pro.

Any ideas on how to get around this would be appreciated.

Tailor

Hi Tailor,

I'm not 100% clear on what your problem is. Is it that you wanted to union two tables and insert this result into a temp table only if data for a particular column (pk I presume) doesn't already exist in the temp table, or is it that you want to union the result of two tables but only select (and thus insert) distinct rows?

Can you provide a bit more info?

Cheers

Rob

|||

Hi Rob,

Thanks for your reply, I'll try to make this a little clearer with some code.

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

From Table1

Where (Id = 'STR001')

UNION

SELECT Description,Id,Reference

FROM Table2

WHERE (Id = 'STR001') AND Description != Table1.Description.

Without the AND statement, all data from both tables is inserted in tempTable, however with the And statement, I get the error message.

'The multi-part identifier Table1.Description could not be bound'

If the description from Table2, matches the Description in Table1, I dont want to include the row in the TempTable.

Sorry if i'm not too clear. At 64, trying to learn VS2005, Sql2005, and intergrating excel in my code, and write a reasonable sized application, all in six months is not something I can recommend. There is just so much to learn, and I dont have a good grasp of a lot of the basics.

If you can suggest a workable solution to my problem, it would be much appreciated.

John

|||

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

From Table1

Where (Id = 'STR001')

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

FROM Table2 t left outer join temptable tt on t.description = tt.description

WHERE (t.Id = 'STR001') AND tt.description is null

Regards

|||

This is a bit of a messy solution

either

1. Insert the data from both tables with a UNION, but place the UNION statement into a subquery and alias the columns to be inserted.

2. Insert data from table1. Then do the same insert from table2 where NOT EXISTS in the temp table (or table1, either will work). This is a common task you will need to learn to do in SQL Server

|||

Hi JMattias and SHughes,

Thank you both, very much, for your replies. It not only solved the problem, I learnt a lot more on the way through.

Your assistance is greatly appreciated.

John

sqlsql

Combine 2 rows from derived table into 1 row w/o repeating query?

I'm trying not to use a temp table, but i may have to do so..

i have a derived table that makes the following results:

ID Status Name

2 1 "A"

2 2 "B"

I want to get the following:

ID Name1 Name2

2 "A" "B"

but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.

Here it is,

Code Snippet

Create Table #data (

[ID] int ,

[Status] int ,

[Name] Varchar(100)

);

Insert Into #data Values('2','1','A');

Insert Into #data Values('2','2','B');

Select

Id

,max(case when Status=1 Then [Name] end) [name1]

,max(case when Status=2 Then [Name] end) [name2]

from

(

Select * from #data -- Your Derived Table

) as Data

Group By

Id

|||

The solution will work, I just needed to think about how to expand it for more columns, but I got it now.

If it's very very slow, I will try something with a CTE - I think that'll work as well.

|||

The CTE was 50-60% faster than the other route! but that method is also useful if using sql2000.|||

Yeah a CTE is going to be the way to go on this in 2005 for sure. Never been a fan of using temp tables and I avoid them when I can. So here's my own variation on the above sample...

Code Snippet

SELECT
[ID],
max(case when Status=1 Then [Name] end) [name1],
max(case when Status=2 Then [Name] end) [name2]

from (
SELECT 2 As [ID], 1 As Status, 'A' As [Name]
UNION SELECT 2, 2, 'B'
) AS Data
GROUP BY [ID]

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