question

Peter.Eden avatar image
Peter.Eden asked

Combine columns from multiple tables

I have numerous (about 40) SQL databases each with a CUSTSUPP table (list of suppliers and addresses) which take the format ABC.CUSTSUPP, DEF.CUSTSUPP, GHI.CUSTSUPP etc I want to bring into one view all the entries in these tables but to include the company prefix from the table so I would end up with something like: Company Code Supplier ABC BT001 Btelecom ABC FG001 FG Supplies CDE BT001 BT Meats CDE FG001 FG Supplies FGH BT001 BTR Ltd FGH FG001 French Greek Ltd The aim is to find duplicate suppliers across the various companies
tablesviews
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
You could do it with static code like this: SELECT 'ABC' as Company, Code, Supplier FROM ABC.dbo.CUSTSUPP UNION ALL SELECT 'DEF' as Company, Code, Supplier FROM DEF.dbo.CUSTSUPP UNION ALL SELECT 'GHI' as Company, Code, Supplier FROM DEF.dbo.CUSTSUPP; If you want to make it more dynamic, you'd have to select into a variable from sys.databases (with some nice where-conditions to filter out only the databases you are interested in) and then execute the stored procedure sp_executesql with the string variable you built up dynamically from your query against sys.databases. The later is a bit more complex, but potentially more flexible.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peter.Eden avatar image
Peter.Eden answered
Thank you. That worked for the first few companies but now I get an error![alt text][1] [1]: /storage/temp/1747-sql+error.jpg All the tables should be the same (it is Exchequer accounts software) and my code is: SELECT 'AQU' as Company, * FROM AQU.CUSTSUPP UNION ALL SELECT 'BC2' as Company, * FROM BC2.CUSTSUPP UNION ALL SELECT 'BC3' as Company, * FROM BC3.CUSTSUPP UNION ALL SELECT 'BC4' as Company, * FROM BC4.CUSTSUPP UNION ALL SELECT 'BCI' as Company, * FROM BCI.CUSTSUPP UNION ALL SELECT 'BFI' as Company, * FROM BFI.CUSTSUPP UNION ALL SELECT 'BPC' as Company, * FROM BPC.CUSTSUPP UNION ALL SELECT 'CEV' as Company, * FROM CEV.CUSTSUPP UNION ALL SELECT 'CHE' as Company, * FROM CHE.CUSTSUPP UNION ALL SELECT 'CHL' as Company, * FROM CHL.CUSTSUPP UNION ALL SELECT 'CRO' as Company, * FROM CRO.CUSTSUPP UNION ALL SELECT 'CRU' as Company, * FROM CRU.CUSTSUPP UNION ALL SELECT 'DAR' as Company, * FROM DAR.CUSTSUPP UNION ALL SELECT 'DUK' as Company, * FROM DUK.CUSTSUPP UNION ALL SELECT 'FER' as Company, * FROM FER.CUSTSUPP UNION ALL SELECT 'GAM' as Company, * FROM GAM.CUSTSUPP UNION ALL SELECT 'GHW' as Company, * FROM GHW.CUSTSUPP UNION ALL SELECT 'GIG' as Company, * FROM GIG.CUSTSUPP UNION ALL SELECT 'GLC' as Company, * FROM GLC.CUSTSUPP UNION ALL SELECT 'GLL' as Company, * FROM GLL.CUSTSUPP UNION ALL SELECT 'GWE' as Company, * FROM GWE.CUSTSUPP UNION ALL SELECT 'HOP' as Company, * FROM HOP.CUSTSUPP UNION ALL SELECT 'HRS' as Company, * FROM HRS.CUSTSUPP UNION ALL SELECT 'IMG' as Company, * FROM IMG.CUSTSUPP UNION ALL SELECT 'LAS' as Company, * FROM LAS.CUSTSUPP UNION ALL SELECT 'LLO' as Company, * FROM LLO.CUSTSUPP UNION ALL SELECT 'LOC' as Company, * FROM LOC.CUSTSUPP UNION ALL SELECT 'MET' as Company, * FROM MET.CUSTSUPP UNION ALL SELECT 'MPT' as Company, * FROM MPT.CUSTSUPP UNION ALL SELECT 'NEW' as Company, * FROM NEW.CUSTSUPP UNION ALL SELECT 'OHH' as Company, * FROM OHH.CUSTSUPP UNION ALL SELECT 'PEB' as Company, * FROM PEB.CUSTSUPP UNION ALL SELECT 'PIC' as Company, * FROM PIC.CUSTSUPP UNION ALL SELECT 'PIN' as Company, * FROM PIN.CUSTSUPP UNION ALL SELECT 'REA' as Company, * FROM REA.CUSTSUPP UNION ALL SELECT 'RED' as Company, * FROM RED.CUSTSUPP UNION ALL SELECT 'SAS' as Company, * FROM SAS.CUSTSUPP UNION ALL SELECT 'SMI' as Company, * FROM SMI.CUSTSUPP UNION ALL SELECT 'SPA' as Company, * FROM SPA.CUSTSUPP UNION ALL SELECT 'TUR' as Company, * FROM TUR.CUSTSUPP UNION ALL SELECT 'VAP' as Company, * FROM VAP.CUSTSUPP UNION ALL SELECT 'VBA' as Company, * FROM VBA.CUSTSUPP UNION ALL SELECT 'VBE' as Company, * FROM VBE.CUSTSUPP UNION ALL SELECT 'VDT' as Company, * FROM VDT.CUSTSUPP UNION ALL SELECT 'VHS' as Company, * FROM VHS.CUSTSUPP UNION ALL SELECT 'VIN' as Company, * FROM VIN.CUSTSUPP UNION ALL SELECT 'VMS' as Company, * FROM VMS.CUSTSUPP UNION ALL SELECT 'VNC' as Company, * FROM VNC.CUSTSUPP UNION ALL SELECT 'VRE' as Company, * FROM VRE.CUSTSUPP UNION ALL SELECT 'VSC' as Company, * FROM VSC.CUSTSUPP UNION ALL SELECT 'VUS' as Company, * FROM VUS.CUSTSUPP UNION ALL SELECT 'WHT' as Company, * FROM WHT.CUSTSUPP

sql error.jpg (31.7 KiB)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's because you have different collation setup. Then you'll have to explicitly set the collation of your output from the queries, to be able to UNION ALL them. Like this: SELECT 'ABC' as Company, Code COLLATE SQL_Latin1_General__CP1_CI_AS, Supplier COLLATE SQL_Latin1_General_CP1_CI_AS FROM ABC.dbo.CUSTAPP UNION ALL SELECT 'DEF' as Company, Code COLLATE SQL_Latin1_General__CP1_CI_AS, Supplier COLLATE SQL_Latin1_General_CP1_CI_AS FROM DEF.dbo.CUSTAPP Or choose whichever collation you want for the output.
1 Like 1 ·
Thanks. I have looked at the extended properties on the tables and they are all SQL_Latin1_General_CP1_CI_AS I have narrowed the faults down to 5 databases, the rest work fine. How can I use "COLLATE SQL_Latin1_General_CP1_CI_AS" if I am selecting all columns? Or do I have to specify each column by name?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.