Answered by:
Below code not working

Question
-
User1979860870 posted
Hi
I have below code but not working. I want to do it without CTE.
I want to insert data into New table from 2 tables.
Insert into #TmpResult
select * from #Tmp
Union All
select * from #Tmp0Thanks
Wednesday, November 11, 2020 2:59 AM
Answers
-
User-2082239438 posted
;WITH CTE AS ( select * from #Tmp Union All select * from #Tmp0 ) Insert into #TmpResult SELECT * FROM CTE;
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, November 11, 2020 2:43 PM -
User452040443 posted
Hi,
Try something like this:
select MyIntColumn, MyVarCharColumn collate SQL_Latin1_General_CP1_CI_AS into #TmpResult from #Tmp Union All select MyIntColumn, MyVarCharColumn collate SQL_Latin1_General_CP1_CI_AS from #Tmp0
Hope this help
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, November 11, 2020 10:54 PM -
User753101303 posted
Seems all your temp tables are not using the same column collation. See https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-ver15
Beyond specifying a collation (ie sorting and comparsion rules for string columns) in the select statement itself double check maybe how those temp tables are created. You don't specify the collation as part of the create statement ?
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, November 12, 2020 1:40 AM
All replies
-
User452040443 posted
Hi,
Try:
select * into #TmpResult from #Tmp Union All select * from #Tmp0
Hope this help
Wednesday, November 11, 2020 12:36 PM -
User-2082239438 posted
;WITH CTE AS ( select * from #Tmp Union All select * from #Tmp0 ) Insert into #TmpResult SELECT * FROM CTE;
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, November 11, 2020 2:43 PM -
User753101303 posted
Hi,
Rather than "not working" always tell which error message you have or which bad behavior you see. For example it won't work if all 3 tables don't have the same column count and the correct column ordering.
It is often suggested to provide an explicit column list rather than using * :
Insert into #TmpResult(a,b,c) select a,b,c from #Tmp Union All select a,b,c from #Tmp0
will work even if columns are not ordered the same way in all 3 tables or if you need to add some addiitional column to a table for another purpose...
Wednesday, November 11, 2020 3:54 PM -
User1979860870 posted
Hi
I get below error
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_CI_AS" in UNION ALL operator.
Thanks
Wednesday, November 11, 2020 4:34 PM -
User452040443 posted
Hi,
Try something like this:
select MyIntColumn, MyVarCharColumn collate SQL_Latin1_General_CP1_CI_AS into #TmpResult from #Tmp Union All select MyIntColumn, MyVarCharColumn collate SQL_Latin1_General_CP1_CI_AS from #Tmp0
Hope this help
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, November 11, 2020 10:54 PM -
User753101303 posted
Seems all your temp tables are not using the same column collation. See https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-ver15
Beyond specifying a collation (ie sorting and comparsion rules for string columns) in the select statement itself double check maybe how those temp tables are created. You don't specify the collation as part of the create statement ?
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, November 12, 2020 1:40 AM