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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.