locked
Below code not working RRS feed

  • 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

    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