Union All or is there a better way?

I have a large number of tables that all contain similar columns but are contextually different. I need to send select columns from all of the tables. There is a template of columns I need to follow and any column I do not have a value for in a particular table I need to send null to keep the format intact. Basically a flat file.

My approach has been to make separate queries from each table and use union all to basically "slap them together" but I wondered if there was a better approach. I do not really have time for an SSIS approach because of time but information on this may be helpful if a periodic feed is needed later.

Thanks for any help
Who is Participating?
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.

Dale FyeCommented:
It is generally inadvisable to have separate tables with the same structure.  Instead, you would normally build a single table with a field (or fields) which describe the differences which are currently causing you to store the data in separate fields.

Yes, using a UNION All is an appropriate way to merge data with similar structure into a single recordset.  On the rare occasion I'm forced to do this, I will generally add one or more fields to each element of the UNION query to indicate what the dataSource was, something like:

SELECT "Table1" as dataSource, Table1.* FROM Table1
SELECT "Table2" as dataSource, Table2.* FROM Table2
Daniel Van Der WerkenIndependent ConsultantCommented:
Another option might be to create a temp table and select from each of the source tables into the temp table and then select from the finalized temp table.

Not sure if that's what you're looking for, but I like temp tables because I can get a step-by-step "status" of the current result set I'm looking at for each phase of my selections.

You could easily use the same column names for different columns too. For example:

IF OBJECT_ID('tempdb.dbo.#MyTempTable') IS NOT NULL DROP #MyTempTable

SELECT ID, [Status] AS Stage FROM dbo.FirstTable

SELECT IKEY AS [ID], Stage FROM dbo.SecondTable

SELECT * FROM #MyTempTable

Open in new window


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
jb916Author Commented:
Dale - The identifier is a good idea. I wonder if I can get away with it within the required format.

Daniel - I like this. I can also create a stored procedure this way.

I forgot to mention that all of the tables will have one or more predicates in common. Date Range, specific type = 1 etc.

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.

jb916Author Commented:
I am sorry I know I could have created a stored procedure from what I already had but I was looking at some modularity it is not that big of a deal anyway.
jb916Author Commented:
Thanks for the tips
Daniel Van Der WerkenIndependent ConsultantCommented:
Note that for me, temp tables don't work so well in stored procedures. I ended up having to use table variables. There is essentially zero difference functionally. I usually develop the SP with temp tables to test it and then convert them to table variables for actual use.
jb916Author Commented:
PS Daniel,
I love lines 1 and 2 of your suggestion. I have seen it before but it looked like to much of a problem to remember. Now that I have tried it things are much easier.

Thanks again
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
Microsoft SQL Server

From novice to tech pro — start learning today.

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.