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 #Tmp0

Thanks

Answer 1

User452040443 posted

Hi,

Try:

select * into #TmpResult from #Tmp
Union All
select * from #Tmp0

Hope this help

Answer 2

User-2082239438 posted

---

;WITH CTE AS 
(
select * from #Tmp
Union All
select * from #Tmp0
)
Insert into #TmpResult
SELECT * FROM CTE;

Answer 3

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...

Answer 4

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

Answer 5

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

Answer 6

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 ?