Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2013 PowerPivot: UNION Tables From 2 Different Non-Linked Microsoft SQL Servers 1

Status
Not open for further replies.

kjv1611

New member
Jul 9, 2003
10,758
US
I'm trying to find a way to UNION (or get the affect of a UNION) 2 tables in Excel. I found a solution that initially sounded like what I wanted using PowerPivot in Excel, but that method is using 2 databases from the same server. What I need to be able to do is the same, but using data from 2 totally different servers which are not linked to each other, so I can't just do it via a linked server.


Also, it looks like that example is using OPENQUERY which apparently can only be used on Linked servers. Even if the servers were linked, last I checked, that FUNCTION is specifically disabled for security reasons.

Does anyone have suggestions? Of course, I could manually append the data to Excel, and then use Data - Remove Duplicates. The total record count should be small enough to not be a big deal, but I wanted to use this instance as a learning tool for bigger such requests I might get in the future.

Thanks for any thoughts

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
HI Steve,

2 tables in Excel. In the same workbook or 2 different workbooks?

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Trying to do in 1 one workbook. Thanks

I can do whatever works best, it'll just be 2 refreshable queries pulling from 2 different SQL Servers. So long as I can combine the 2 into one in the end, removing dups, I'll be happy. I'm also limiting the column selection to just 5 columns for simplicity sake.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
BTW, You are one amazing and busy bee, Skip! [smile]

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
You can do a Union query in MS Query.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Hmm, yeah, maybe that'll be best here. Thanks. The good news is the workbook could just as well sit in one place without renaming, so I should be fine using MS Query. In some past projects, MS Query went wonky, not allowing me to update the workbook name if renamed or moved. I think I'll go the MS Query route on this one for sure. Thanks!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
There's a VBA fix for that. When I used MS Query in production, I'd always run code to do my updates/refreshes. If ALWAYS construct the Connection String for the workbook.path & workbook.name.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Thanks for that tip.

Now, for some reason, it's telling me it doesn't see any tables within the workbook, but I've got several tables, 2 of which I want to UNION.

Does it work differently with QueryTables? For instance, I know sometimes when you link from source A -> B -> C, C doesn't like pulling from B which pulls from A.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
You know, I'm probably just skipping a step, Skip. [blush]

Let me slow down a bit.

I tested on a few different workbooks, those with QueryTables and those with copy/pasted data, and same message. I'm probably jumping the gun by hitting [Enter] instead of working with MSQuery's buttons.

I'm about to head out for lunch and an errand, so I'll retackle this after a short while.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
In the Add Tables Window in MS Query, hit the Options button and check all boxes.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Thanks again! Duh! I forgot about the checkboxes. SystemTables has it.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top