Conquering errors in T-SQL and SSIS: Act 1 Scene 1

Problem background: So I have four tables in a database. Table1 has four columns, Table2 has 3 columns, Table3 has eight columns and Table4 has 10 columns. Table3 and Table4 have a same column called Year. The datatype for the Year column is nvarchar(255). The data in the Year column in Table4 is coded as 2001-02, 2002-03 and so forth. All four tables have one common column called as schoolCode dataype is varchar(255).

Problem objective:
To write a T-SQL query that can extract all columns in all four tables based on the year and then save it into a new table.

My initial faulty solution: 

I wrote a T-SQL query  select * from Table3 union select * from Table4 left join Table3
on Table3.year=Table4.Year

When I executed this query, I got the following error
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Solution:

Make sure that all the statements using UNION, INTERSECT or EXCEPT operator have same number of expressions(columns).
Use column names instead of using * in the select list.

So the correct T-SQL query should be

select Table3.column1, Table3.column2 from Table3 union select Table4.column4, Table4.column8 left join Table3 on Table3.year=Table4.Year

Ensure that in the select statement you have an equal number of columns selected from the selected tables. What I mean is that if suppose you select two columns from Table3 and six columns from Table4 then you will again get this error on run time or on executing the above query.

Advertisements