6 Replies - 5405 Views - Last Post: 13 June 2012 - 11:04 AM

#1 BattlFrog  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 73
  • Joined: 09-April 12

How to UNION two tables with different column counts

Posted 11 June 2012 - 03:52 PM

Greetings. I am attempting to UNION two tables with differing number of columns but I am getting the "must have equal number of expressions in target list" error. I know the column count must be the same when using a UNION, but I though it referred to the number of columns IN THE QUERY, not the actual tables. So the actual number of columns in the tables need to match, regardless of whether or not they are in the query?

I believe the solution will be to get a count of the tables, and add some "filler" (null columns) into the side of the query with less columns in the table, is that correct?

Is This A Good Question/Topic? 0
  • +

Replies To: How to UNION two tables with different column counts

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13483
  • View blog
  • Posts: 53,820
  • Joined: 12-June 08

Re: How to UNION two tables with different column counts

Posted 11 June 2012 - 04:05 PM

Well yes.. the count must be right as well as the data types. I mean it makes no sense to union up two tables and match a row for 'name' with a row for 'phone number'.

What's your query looking like?
Was This Post Helpful? 0
  • +
  • -

#3 BattlFrog  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 73
  • Joined: 09-April 12

Re: How to UNION two tables with different column counts

Posted 13 June 2012 - 08:03 AM

Thanks Modi, I forgot about the datatype thing, though your right it shoulb be a no brainer. I got the column amount matching and the datatypes (had to cast a couple) and now the query runs well in SSMS. I copied the query into an SSIS 2008 package, but it wont run due to an error:

"Error: 0xC020901C at Data Flow Task, OLE DB Source [1]: There was an error with output column "GraduationYear" (797) on output "OLE DB Source Output" (11). The column status returned was: "The value could not be converted because of a potential loss of data."

I had cast that column from numeric to varchar as part of my script, and as I mentioned above it runs fine in SSMS. However, I checked the metadata in the SSIS package and it shows the output datatype as Numeric which I assume is the problem.

So, is there a way to force SSIS to honor the CAST I have in the query?
Was This Post Helpful? 0
  • +
  • -

#4 BattlFrog  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 73
  • Joined: 09-April 12

Re: How to UNION two tables with different column counts

Posted 13 June 2012 - 10:34 AM

Fixed it by deleting the ODE DB source and recreating it. I guess the metadata got all screwy or something.
Was This Post Helpful? 1
  • +
  • -

#5 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13483
  • View blog
  • Posts: 53,820
  • Joined: 12-June 08

Re: How to UNION two tables with different column counts

Posted 13 June 2012 - 10:53 AM

Good job on working it out!
Was This Post Helpful? 0
  • +
  • -

#6 BattlFrog  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 73
  • Joined: 09-April 12

Re: How to UNION two tables with different column counts

Posted 13 June 2012 - 10:58 AM

One thing I'm good at is banging my head against a wall untill I come up with a good idea or I pass out. :punk:
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13483
  • View blog
  • Posts: 53,820
  • Joined: 12-June 08

Re: How to UNION two tables with different column counts

Posted 13 June 2012 - 11:04 AM

Shockingly that's a handy skill to have.. though wear a helmet - you don't want to rattle too many screws loose!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1