gserdijn (3/26/2009)
When the SELECT statement is compiled for the INSERT, the first value is implicitly taken as an INT. In the UNIONs, as each SELECT is added, they must match this data type.
If that is the correct explanation, would the next statement work?
Insert into test Select 'A' union Select 1;
(Answer: No)
You're quite right. The data type isn't determined by the first value, it's determined by data type precedences
see here for SQL2008 http://msdn.microsoft.com/en-us/library/ms190309.aspx
here for SQL 2005 http://msdn.microsoft.com/en-us/library/ms190309(SQL.90).aspx
int beats varchar hands-down everytime!
Kev