48278

Build a view to query multiple tables with identical column names

Question:

I'm trying to build a view so I can write a single query against it:

FROM Datebase.dbo.[AllQtrs]

Using SQL Server's 'Create view' function, it spits out the following:

SELECT dbo.[2010 Q3].*, dbo.[2010 Q4].*, dbo.[2011 Q1].*, dbo.[2011 Q2].*, dbo.[2011 Q3].*, dbo.[2011 Q4].*, dbo.[2012 Q1].*, dbo.[2012 Q2].*, dbo.[2012 Q3].*, dbo.[2013 Q2].*, dbo.[2013 Q1].*, dbo.[2012 Q4].*, dbo.[2014 Q1].*, dbo.[2013 Q4].*, dbo.[2013 Q3].*, dbo.[2014 Q2].* FROM dbo.[2010 Q3] CROSS JOIN dbo.[2010 Q4] CROSS JOIN dbo.[2011 Q1] CROSS JOIN dbo.[2011 Q2] CROSS JOIN dbo.[2011 Q3] CROSS JOIN dbo.[2011 Q4] CROSS JOIN dbo.[2012 Q1] CROSS JOIN dbo.[2012 Q2] CROSS JOIN dbo.[2012 Q3] CROSS JOIN dbo.[2012 Q4] CROSS JOIN dbo.[2013 Q1] CROSS JOIN dbo.[2013 Q2] CROSS JOIN dbo.[2013 Q3] CROSS JOIN dbo.[2013 Q4] CROSS JOIN dbo.[2014 Q1] CROSS JOIN dbo.[2014 Q2]

All of my tables have the identical column names/properties. The error message I receive is

<blockquote>

Column names in each view or function must be unique. Column name XY is specified more than once.

</blockquote>

The goal is to query all tables by just querying against the AllQtrs view. Any help?

Answer1:

You must specify the names manually and define unique names for them. This way you'll show the columns. An example would be

SELECT dbo.[2010 Q3].Column1 AS 2010Q3Column1, dbo.[2010 Q3].Column2 As 2010Q3Column2, dbo.[2010 Q3].Column1 As 2010Q4Column1 etc.

Btw, make sure that CROSS JOIN is really what you want - looks like you actually want to use UNION ALL here. CROSS JOIN will return cartesian product of all rows from all tables, i.e. if there are 10 rows in each table, it will be 10^16 records, which definitely won't complete in a few decades.

With UNION ALL it will look like:

SELECT Column1, Column2 {add here the remaining column names} FROM dbo.[2010 Q3] UNION ALL SELECT Column1, Column2 {add here the remaining column names} FROM dbo.[2010 Q4] UNION ALL SELECT Column1, Column2 {add here the remaining column names} FROM dbo.[2011 Q1] UNION ALL ...etc

Recommend

  • how to display json data using ng-repeat?
  • Grid, Selecting Header_Id from one and Content values from another table and pushing “value with Id”
  • C# ASP.NET MVC Create PDF from view Rotativa or iTextSharp? [closed]
  • using sqlldr from java
  • Oracle regexp match patterns in string that includes new line character
  • How to configure aptana for instant running of my script
  • Is index used for 'outer' and 'inner' where clauses in nested selects?
  • Sharepoint, Calculated column, IF function and date
  • How to use discontinuous range in SUMIF
  • Get count of row and sum group by quarter of date, if another column doesnt exits a value in SQL Ser
  • Lucene Query Boosting
  • std::string initialization with a bool
  • How to implement interleaved page allocation in a user-mode NUMA-aware memory allocator?
  • How to publish messages on RabbitMQ with fanout exchange using Spring Boot
  • SQLite callback efficient solution
  • Django: Serializing a list of multiple, chained models
  • Cross compiling Qt 5
  • Making a URL W3C valid AND work in Ajax Request
  • Aligning image on right side of title on UIButton with imageEdgeInsets and titleEdgeInsets
  • read values from form post in jquery or javascript
  • apply a javascript function to draggable copy
  • Consuming a WCF service in a Java Client using wsHttpBinding
  • Exception gevent.hub.LoopExit: LoopExit('This operation would block forever',)
  • Azure table store snapshot/backup capability
  • Bigquery event streaming and table creation
  • Django: Count of Group Elements
  • Avoid links criss cross / overlap in d3.js using force layout
  • NHibernate Validation Localization with S#arp Architecture
  • Linq Objects Group By & Sum
  • How can I send an e-mail from a vbs script
  • Accessing IRQ description array within a module and displaying action names
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • Large data - storage and query
  • How to get next/previous record number?
  • Hits per day in Google Big Query
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Getting Messege Twice Using IMvxMessenger
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass