Sql multiple INSERTs INTO @Temp table not cumulative??

I am conditionally getting records from up to 8 different tables, and then removing duplicates.  Below is a simplified example of the part that is not working...

DECLARE @TempTbl table
(CID int, Str nvarchar(50))

INSERT INTO @TempTbl
SELECT FirstTable.CID, FirstTable.Str
FROM FirstTable
--WHERE condition causes 10 records to be inserted

INSERT INTO @TempTbl
SELECT SecondTable.CID, SecondTable.Str
FROM SecondTable
--WHERE condition causes 5 records to be inserted

SELECT * FROM @TempTable --SELECT DISTINCT, I removed the DISCINCT for testing and development
--expected 15 records only got 5, seems like the @TempTble is getting cleared

What is wrong with this and/or what is the proper way to achieve the same thing??

Thanks Much
Sam
SamCashAsked:
Who is Participating?
 
dsackerContract ERP Admin/ConsultantCommented:
If you're only getting 5 records without DISTINCT, you may have missed running one of those INSERTs. Perhaps you can try it this way, without a @TempTable:
SELECT FirstTable.CID, FirstTable.Str
FROM FirstTable
UNION
SELECT SecondTable.CID, SecondTable.Str
FROM SecondTable

Open in new window

How many do you get that way?

UNION will result in distinct values. To see them call, change it to UNION ALL. Try it both ways.
0
 
arnoldCommented:
I am not sure what you are asking, the @temptbl does not have any constraint so however many rows you get from the select as many will be added/inserted into the @temptbl.

Unfortunately, other than you would like to remove duplicates, the question does not present with what you issue is.

Your best bet is to run a group by query with a counter to indicate how many of X type of records you have, and then use that to clear out those you do not want while maintaining one.

The sequence is at issue, you declare the @temptbl and then you insert the first 10 and then the second query inserts the next 5 or do you ?

One option is to use a temptable that spans sessions ##temporary_table any session can access it and any session can delete items from it.
0
 
SamCashAuthor Commented:
Thank you both,

This is one sproc.  I am passed two parameters, an EntityID and a UserID.  One table is contacts, other tables are the positions the contacts have and their relationship with other contacts and entities.  As an example, an Associations, Leagues, Conferences, Divisions, Teams and Players.  There is a Table for each.  Each entry in each table has one or more entries in a Manager(s) Table.  All the entities, officers, managers, players all have brought in other contacts into the organization which are records in the contacts table with a CreatedBy field. Many contacts have multiple roles.  All the aforementioned tables are just ID's except the contact table which has limited information about the contact with lots of id's to yet more table for addresses, etc. I am required to return all the related contacts where the userID and entityID where UserID is related. directly or indirectly through all the entity tables, plus the co-managers from the entities the user is co manager and all the contacts created by the user, plus the user himself.  So a compact sql quey gave me a headache.  My design was to simply query each table for the relationship required with that particular table, and put the results into (insert into select) a temporary table, this temp table would grow over the 8 querys resulting in a complete list with some duplicates (when people have multiple roles).  At the end I figured I would just Select Distinct all records from the temp table to return to the final record set to the c# code behind.

Everything is working, I tested each select query for proper results, all the logic is working.  The problem is the temp table  seems to clear between each block of INSERT INTO SELECT FROM statements.  

How do I create a temporary table (local variable @table or #table) so I can accumulate the results of several queries  to return as the final result.

I just briefly read about UNION, I think I am supposed to use UNION...  Thanks again, brb I am going to try UNION

Sam
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Walter RitzelSenior Software EngineerCommented:
I think to simplify things, change your code to use only one insert, using the UNION statement.

INSERT INTO @TempTbl
SELECT FirstTable.CID, FirstTable.Str
FROM FirstTable
UNION
SELECT SecondTable.CID, SecondTable.Str
FROM SecondTable

Open in new window

0
 
SamCashAuthor Commented:
Hey,

UNION worked like a hot knife through butter!  

I wish I had more experience with sql, I see this basic operator's purpose is for exactly what I was trying to do.

Thanks for contributing to my experience...
Sam
0
 
SamCashAuthor Commented:
Hey,

UNION worked like a hot knife through butter!  

I wish I had more experience with sql, I see this basic operator's purpose is for exactly what I was trying to do.

Thanks for contributing to my experience...
Sam
0
 
dsackerContract ERP Admin/ConsultantCommented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.